Checking to see a sheet of the same name exsists

Hi,

I orginally started this code by recording it from a macro and then have put in some vba surrounding it so its a bit messy! The code is behind a button on a user form. What it does is create a copy of a sheet called "Master" and then names it the date which has been entered on to a text box - tbDate -on a user form. What I would like to do is check to see if the name that has been entered in to tbDate is already a sheet name and if so stop the procedure and pop up with a text box, but if it doesn't exsist then keep on doing the rest of the code. I've tried doing it with some code I found on http://www.ozgrid.com/VBA/IsWorkbookOpen.htm which states:

Sub DoesSheetExist()
'''''''''''''''''''''''''''''''''''''
'Written by www.OzGrid.com

'Test to see if a Worksheet exists.
'''''''''''''''''''''''''''''''''''''

Dim wSheet As Worksheet

	On Error Resume Next
	Set wSheet = Sheets("Sheet1")
		If wSheet Is Nothing Then 'Doesn't exist
			MsgBox "Worksheet does not exist", _
			vbCritical,"OzGrid.com"
			Set wSheet = Nothing
			On Error GoTo 0
		Else 'Does exist
			MsgBox "Sheet 1 does exist", _
                                vbInformation,"OzGrid.com"
			Set wSheet = Nothing
			On Error GoTo 0
		End If
End Sub

But I couldn't get this working with my code, or with using information from tbDate

The code which I am working with is:

Private Sub cmdNewSheet_Click()
On Error GoTo Err_Command1_Click

Dim date1 As Long
Application.ScreenUpdating = False
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Name = "1"
    Sheets("1").Select
    Sheets("Master").Select
    Cells.Select
    Selection.Copy
    Sheets("1").Select
    Range("A1").Select
    ActiveSheet.Paste
    Range("A1").Select
    Sheets("Master").Select
    Range("A1").Select
    Application.CutCopyMode = False
    Sheets("1").Select
    Range("b1").Select
    Selection.ClearContents
    ActiveCell.Value = Me.tbDate.Value
    Application.CutCopyMode = False
    
    'Puts the date in the correct format and then enters it as the sheets name
    tbDate.Value = Format(tbDate.Value, "dd-mm-yy")
    Sheets("1").Name = Me.tbDate.Value
    
    Range("A1").Select
    
    Unload frmEnterDate
    ActiveWindow.zoom = 70
    
    Application.ScreenUpdating = True
Exit_Command1_Click:
    Exit Sub

Err_Command1_Click:
    MsgBox ("The sheet you created already exsists!")
    'Delete a sheet
Application.DisplayAlerts = False
Sheets("1").Delete
'ActiveSheet.Delete
'ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
    
   
    
    Resume Exit_Command1_Click
End Sub

Any help would be very much appericated, I hope all this makes sense!

Thanks a lot,

Dave

Hi Dave,

Add this code to the beginning of Private Sub cmdNewSheet_Click()
--------------
Dim sht As Worksheet
Dim str As String

str = Me.tbDate.Value
For Each sht In ThisWorkbook.Worksheets
If sht.Name = str Then
MsgBox "A sheet with this name already exists", vbOKOnly +
vbExclamation, str
Exit Sub
End If
Next
------------------
This will loop thru all the existing sheets and checkes there names.


Hoop This Helps,


Executor

One way:

Private Sub cmdNewSheet_Click()
Dim sTemp As String
Dim wsTest As Worksheet
With tbDate
If IsDate(.Text) Then
sTemp = Format(.Text, "dd-mm-yy")
On Error Resume Next
Set wsTest = Worksheets(sTemp)
On Error GoTo 0
If Not wsTest Is Nothing Then
MsgBox "The sheet you created already exists!"
Else
Worksheets("Master").Copy After:=Sheets(Sheets.Count)
With Sheets(Sheets.Count)
.Name = sTemp
.Range("B1").Value = sTemp
End With
End If
ActiveWindow.Zoom = 70
End If
End With
Application.ScreenUpdating = True
Unload Me
End Sub



In article <[email protected]>,
beans_21 <[email protected]> wrote:

> I orginally started this code by recording it from a macro and then
> have put in some vba surrounding it so its a bit messy! The code is
> behind a button on a user form. What it does is create a copy of a
> sheet called "Master" and then names it the date which has been entered
> on to a text box - tbDate -on a user form. What I would like to do is
> check to see if the name that has been entered in to tbDate is already
> a sheet name and if so stop the procedure and pop up with a text box,
> but if it doesn't exsist then keep on doing the rest of the code.

Thank you both for your responses! I decied to use JE McGimpsey, it worked perfectly, thank you so much for your help

Dave