Help with VBA Macro

[jimvola]

I am trying to make a macro that accepts a date from a user. The macro then creates a worksheet and names it the date. The problem that I have is that some users enter in one of the symbols that is restricted when naming worksheets. I have used a errorhandler routine and it works the first time, but does not work if the user reenters the improper date. How can I fix this? The code is as follows. Thanks in advance.

Sub New_month()

Application.ScreenUpdating = False
On Error GoTo errorhandler
Dim startingdate As String
Dim num As Integer
startingdate = InputBox("Please enter the starting date for the new schedule! Please enter in month date format. (ie. 2-10)")
num = Sheets.Count
Sheets("Master").Unprotect
Sheets("Master").Copy After:=Sheets(num)
Sheets("Master").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Sheets(num + 1).Select

start:
range("g1").Select
ActiveCell = startingdate
range("g3").Select
Sheets(num + 1).Name = startingdate
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Exit Sub

errorhandler:
MsgBox ("Please do not use any symbols other than - for the date")
startingdate = InputBox("Please enter the starting date for the new schedule! Please enter in month date format. (ie. 2-10)")
GoTo start
End Sub

 

[Legacy 98055]

Hi Jim.
This is kinda wordy, but this is how I would do it...
Replace this line of code...<pre>
startingdate = InputBox("Please enter the starting date for the new schedule! Please enter in month date format. (ie. 2-10)")
</pre>

...with all of this...<pre>
TryAgain:
startingdate = InputBox("Please enter the starting date for the new schedule! Please enter in month date format. (ie. 2-10)")

If startingdate = "" Then Exit Sub

If Not IsDate(startingdate) Then
MsgBox "You entered an invalid date. Please re-enter."
GoTo TryAgain
End If

If InStr(startingdate, "/") <> 0 Then
MsgBox "Your date is valid, but Excel does not " & Chr(13) & _
"allow the ""/"" character when naming a worksheet." & Chr(13) & _
"Please re-enter you date using ""-"" instead."
GoTo TryAgain
End If

</pre>
All other invalid characters should be filtered out by the IsDate function.

Tom

 

[jimvola]

Tom,
Thanks for the quick response. I am amazed at the speed. I too tried the isdate function but it does not work in the macro. If I enter in a date such as 52-02, the macro creates the worksheet naming it 52-02. However, as you noticed, I have to use a date in the worksheet also. Is there another option?

Jim

 

[jimvola]

Tom,
The change did work like a charm!

Jim