Help with VBA Macro

jimvola

New Member
Joined
May 30, 2002
Messages
46
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
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
L

Legacy 98055

Guest
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

New Member
Joined
May 30, 2002
Messages
46
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,611
Members
414,080
Latest member
penguin23

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top