Checking to see a sheet of the same name exsists

[beans_21]

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:


Code:
--------------------
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:


Code:
--------------------
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

 

[Executor]

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

 

[JE McGimpsey]

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

 

[beans_21]

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

Dave