Check if Worksheet exists

[Cisco7970]

Hi,

I am creating a worksheet in VBA, I would like to create an intelligence, if I run the macro again, it will check if the worksheet exists already, if it exists it will update the existing one. If it doesn't exist, it will create it.
At the moment, with the code below, I am creating my worksheet

Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = txtName

txtName is a field which user fills in and Worksheet is created with that name. How can I achieve this?
Thank you.

Cisco 7970

 

[Misca]

The following function should do the trick:

Public Function WorksheetExists(ByVal WorksheetName As String) As Boolean 

On Error Resume Next
WorksheetExists = (Sheets(WorksheetName).Name <> "")
On Error GoTo 0

End Function

 

[Cisco7970]

But where do you create the Worksheet as in my example?

Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = txtName

Can you please adjust the code with this?
Thank you

 

[Misca]

This is how you use the function within your code:

If WorksheetExists(txtName) = False Then
    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = txtName
End If
Sheets(txtName).Activate

The code checks if the worksheet exists and creates it if needed. Then it activates the sheet called txtName.

 

[Cisco7970]

I get the error below.

Compile error:
Variable not defined

If WorksheetExists(txtName) = False Then

 

[Misca]

In your first post you said txtName is filled in by the user. You need to give it a value before you can use it.

 

[Misca]

Here's simple stand alone code:

Sub AddSheetIfNeeded()
Dim txtName As String
txtName = InputBox("Type the sheet name", "Sheet Name ?")
If WorksheetExists(txtName) = False Then
    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = txtName
End If
Sheets(txtName).Activate
End Sub
Public Function WorksheetExists(ByVal WorksheetName As String) As Boolean
On Error Resume Next
WorksheetExists = (Sheets(WorksheetName).Name <> "")
On Error GoTo 0
End Function

 

[Sektor]

[COLOR="Blue"]Function[/COLOR] SheetExists(SheetName [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR]) [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Boolean[/COLOR]
    [COLOR="Blue"]Dim[/COLOR] sh [COLOR="Blue"]As[/COLOR] Worksheet
    [COLOR="Blue"]On[/COLOR] [COLOR="Blue"]Error[/COLOR] [COLOR="Blue"]Resume[/COLOR] [COLOR="Blue"]Next[/COLOR]
    [COLOR="Blue"]Set[/COLOR] sh = Worksheets(sh)
    [COLOR="Blue"]If[/COLOR] [COLOR="Blue"]Not[/COLOR] sh [COLOR="Blue"]Is[/COLOR] [COLOR="Blue"]Nothing[/COLOR] [COLOR="Blue"]Then[/COLOR] SheetExists = [COLOR="Blue"]True[/COLOR]
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Function[/COLOR]

 

[Cisco7970]

Apologizes,
I still struggle with this where to put the condition, below is my code and user fills a userform where txtName is defined. The UserForm feeds the Main sheet and also a new sheet is created with the name of txtName and also some values are copied to the new sheet.

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Main")

iRow = ws.Cells(Rows.Count, 1) _
  .End(xlUp).Offset(1, 0).Row

ws.Cells(iRow, 1).Value = Me.txtFile.Value
ws.Cells(iRow, 2).Value = Me.txtDate.Value
ws.Cells(iRow, 3).Value = Me.txtName.Value
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = txtName
Set ws = Worksheets(Me.txtName.Value)
ws.Cells(iRow, 1).Value = Me.txtFile.Value
ws.Cells(iRow, 2).Value = Me.txtDate.Value
ws.Range("A1:C1") = Array("File", "Date")

Me.txtFile.Value = ""
Me.txtDate.Value = ""
Me.txtChauffeur.Value = ""
Me.txtFile.SetFocus
End Sub
Private Sub cmdClose_Click()
  Unload Me
End Sub

 

[Cisco7970]

Can someone please advice where I should put the condition? Above is my code.

Thank you very much.