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

 

[Sektor]

...
ws.Cells(iRow, 1).Value = Me.txtFile.Value
    ws.Cells(iRow, 2).Value = Me.txtDate.Value
    ws.Cells(iRow, 3).Value = Me.txtName.Value
   [B] [COLOR="Blue"]If[/COLOR] SheetExists(txtName.Text) [COLOR="Blue"]Then[/COLOR]
        Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = txtName
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]If[/COLOR][/B]
    [COLOR="Blue"]Set[/COLOR] ws = Worksheets(Me.txtName.Value)
    ws.Cells(iRow, 1).Value = Me.txtFile.Value
    ws.Cells(iRow, 2).Value
...

 

[Cisco7970]

Compile error
Sub or Function not defined

I see that SheetExists is not defined, where can I define it by looking at my code?

Thank you.

 

[Sektor]

SheetsExists function

 

[Cisco7970]

I know, my issue is there.
Should I put the Function above "Private Sub cmdAdd_Click()"?

Function WorksheetExists(SheetName As String) As Boolean
Private Sub cmdAdd_Click()

I would appreciate if you may integrate SheetsExists function in my code and provide it to me so I will have a better understanding.

many thanks.

 

[Sektor]

Just put this function into standard module.

 

[Cisco7970]

Compile error
Expected End Sub

What I am trying is, it will create the sheet if it doesn't exist and if it exists, it will update it.

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
Function SheetExists(SheetName As String) As Boolean
    Dim sh As Worksheet
    On Error Resume Next
    Set sh = Worksheets(sh)
    If Not sh Is Nothing Then SheetExists = True
End Function
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

 

[Sektor]

Create some module (Insert -> Module) and place function there.

 

[Cisco7970]

If I create a module and insert SheetsExists and keep my code as it is, I get the error below when I use my UserForm for the second time.

Run-time error '1004':
Canot rename a sheet to the same name as another sheet, a referenced object library or a workbook referenced by Visual Basic.

 

[Sektor]

Ah, my bad... Add Not:

If [B][COLOR="Red"]Not[/COLOR][/B] SheetExists(txtName.Text) Then
...

 

[Cisco7970]

I get the same error

Run-time error '1004':
Canot rename a sheet to the same name as another sheet, a referenced object library or a workbook referenced by Visual Basic.

Can you please provide me the entire code I need to use?
Thank you.<!-- / message -->