Trying to stop making a new sheet
 

I have the following code but can't get it to work. Essentially, when the
macro is run a new sheet is made and the new name assigned is the day that
the macro is run. I want it to stop, not make the new sheet and close the
UserForm if a sheet of that name already exists (along with the msgbox
explaining what happend). I assume that the problem is common from different
data types and have tried CInt, CVar, etc... with no luck. Where am I going
wrong.
Thanks in advance for any assistance.
Brad K

Private Sub cbOK_Click()
Dim i As Integer
Dim j As Integer
Dim xDay As Integer
Dim xMonth As Integer
Dim xYear As Integer

i = Worksheets.Count
For j = 1 To i
If Worksheets(j).Name = Day(Calendar1.Value) Then
MsgBox "The date you have chosen already exists as a sheet."
Unload Me
End If
Next j
Worksheets("Template").Copy Befo=Worksheets(i)
xYear = Year(Calendar1.Value)
xMonth = Month(Calendar1.Value)
xDay = Day(Calendar1.Value)
Cells(7, 9) = xMonth & "/" & xYear
Cells(7, 9).NumberFormat = "mmmm/yy"
Worksheets(i).Name = xDay
Unload Me
End Sub

Trying to stop making a new sheet
 

see if this works:

Private Sub cbOK_Click()
Dim i As Integer
Dim j As Integer
Dim xDay As Integer
Dim xMonth As Integer
Dim xYear As Integer

i = Worksheets.Count
For j = 1 To i
If Worksheets(j).Name = cStr(Day(Calendar1.Value)) Then ' <==
MsgBox "The date you have chosen already exists as a sheet."
Unload Me
Exit sub ' <===
End If
Next j
Worksheets("Template").Copy Befo=Worksheets(i)
xYear = Year(Calendar1.Value)
xMonth = Month(Calendar1.Value)
xDay = Day(Calendar1.Value)
Cells(7, 9) = xMonth & "/" & xYear
Cells(7, 9).NumberFormat = "mmmm/yy"
Worksheets(i).Name = xDay
Unload Me
End Sub

--
Regards,
Tom Ogilvy


"Brad K." wrote in message
...
I have the following code but can't get it to work. Essentially, when the
macro is run a new sheet is made and the new name assigned is the day that
the macro is run. I want it to stop, not make the new sheet and close the
UserForm if a sheet of that name already exists (along with the msgbox
explaining what happend). I assume that the problem is common from
different
data types and have tried CInt, CVar, etc... with no luck. Where am I
going
wrong.
Thanks in advance for any assistance.
Brad K

Private Sub cbOK_Click()
Dim i As Integer
Dim j As Integer
Dim xDay As Integer
Dim xMonth As Integer
Dim xYear As Integer

i = Worksheets.Count
For j = 1 To i
If Worksheets(j).Name = Day(Calendar1.Value) Then
MsgBox "The date you have chosen already exists as a sheet."
Unload Me
End If
Next j
Worksheets("Template").Copy Befo=Worksheets(i)
xYear = Year(Calendar1.Value)
xMonth = Month(Calendar1.Value)
xDay = Day(Calendar1.Value)
Cells(7, 9) = xMonth & "/" & xYear
Cells(7, 9).NumberFormat = "mmmm/yy"
Worksheets(i).Name = xDay
Unload Me
End Sub

Trying to stop making a new sheet
 

Worked great Tom. I'm glad to know I was on the right path. Thanks much for
the help.
Brad

"Tom Ogilvy" wrote:

see if this works:

Private Sub cbOK_Click()
Dim i As Integer
Dim j As Integer
Dim xDay As Integer
Dim xMonth As Integer
Dim xYear As Integer

i = Worksheets.Count
For j = 1 To i
If Worksheets(j).Name = cStr(Day(Calendar1.Value)) Then ' <==
MsgBox "The date you have chosen already exists as a sheet."
Unload Me
Exit sub ' <===
End If
Next j
Worksheets("Template").Copy Befo=Worksheets(i)
xYear = Year(Calendar1.Value)
xMonth = Month(Calendar1.Value)
xDay = Day(Calendar1.Value)
Cells(7, 9) = xMonth & "/" & xYear
Cells(7, 9).NumberFormat = "mmmm/yy"
Worksheets(i).Name = xDay
Unload Me
End Sub

--
Regards,
Tom Ogilvy


"Brad K." wrote in message
...
I have the following code but can't get it to work. Essentially, when the
macro is run a new sheet is made and the new name assigned is the day that
the macro is run. I want it to stop, not make the new sheet and close the
UserForm if a sheet of that name already exists (along with the msgbox
explaining what happend). I assume that the problem is common from
different
data types and have tried CInt, CVar, etc... with no luck. Where am I
going
wrong.
Thanks in advance for any assistance.
Brad K

Private Sub cbOK_Click()
Dim i As Integer
Dim j As Integer
Dim xDay As Integer
Dim xMonth As Integer
Dim xYear As Integer

i = Worksheets.Count
For j = 1 To i
If Worksheets(j).Name = Day(Calendar1.Value) Then
MsgBox "The date you have chosen already exists as a sheet."
Unload Me
End If
Next j
Worksheets("Template").Copy Befo=Worksheets(i)
xYear = Year(Calendar1.Value)
xMonth = Month(Calendar1.Value)
xDay = Day(Calendar1.Value)
Cells(7, 9) = xMonth & "/" & xYear
Cells(7, 9).NumberFormat = "mmmm/yy"
Worksheets(i).Name = xDay
Unload Me
End Sub