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:

Please Login or Register  to view this content.

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

The code which I am working with is:

Please Login or Register  to view this content.

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



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.

[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