Searching and creating worksheets 1

[plip1978]

Can anyone suggest why the following code doesn't work? I want to search for a worksheet with the "username_temp" and if it doesn't exist then create a new worksheet.
It works sometimes, but when I've used it once, then logout and login at another terminal it tries to create a new sheet and then obviously can't rename it "username_temp" because that worksheet already exists.
I hope you can help!!
Thanks in advance.

Private Sub Workbook_Open()

Dim CurrentUser

CurrentUser = Application.UserName
TempName = CurrentUser & "_Temp"

For Each ws In Worksheets
If ws.Name = TempName Then
Worksheets(TempName).Activate
Exit For
Else
Worksheets.Add
With ActiveSheet
.Name = TempName
.Cells(5, 3).Value = "Current User:"
End With
End If
Next ws

End Sub

 

[MikeGeitner]

If there isn't a sheet named "username_temp", it's trying to add one on each loop. On the second time around, it fails. This might work:

Private Sub Workbook_Open()

Dim CurrentUser
Dim WS As Worksheet
Dim TempName As String
Dim wsBoo As Boolean

wsBoo = False

CurrentUser = Application.UserName
TempName = CurrentUser & "_Temp"

For Each WS In Worksheets

    If WS.Name = TempName Then
       wsBoo = True
            Exit Sub
    End If
Next

If wsBoo = False Then
 
 Worksheets.Add
  With ActiveSheet
     .Name = TempName
     .Cells(5, 3).Value = "Current User:"
    End With
  
End If


End Sub

 

[WinblowsME]

Private Sub Workbook_Open()
   On Error Resume Next
   
   If Len(Sheets(Application.UserName).Name) = 0 Then
      Sheets.Add after:=Sheets(Sheets.Count)
      Sheets(Sheets.Count).Name = Application.UserName
   End If
End Sub