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 :) :) :)
--
beans_21
------------------------------------------------------------------------
beans_21's Profile:
http://www.excelforum.com/member.php...o&userid=30281
View this thread:
http://www.excelforum.com/showthread...hreadid=501031