Alternative to "On Error GoTo 0" that takes macro to last command?

S

StargateFan

I've read and read on the archives but I'm obviously not plugging in
right search terms as I'm not familiar with the required syntax to
begin with <g>. Same with the help file.


In box code below, there is a simple error handling "On Error GoTo 0".
What can this be replaced with so that if user changes mind and clicks
cancel, the macro will just go to the last line and invoke the
"ActiveSheet.Protect"?

****************************************************
Sub RequestDateFromUser()
'
ActiveSheet.Unprotect 'place at the beginning of the code
On Error Resume Next
ActiveSheet.ShowAllData
On Error GoTo 0

Dim vResponse As Variant
Do
vResponse = Application.InputBox( _
Prompt:="Enter a start date." & vbCrLf & vbCrLf & _
"(By the way, Excel is pretty forgiving of the date
style you use when you enter that date.)", _
Title:="Health Log", _
Default:=Format(Date, "yyyy/mm/dd"), _
Type:=2)
If vResponse = False Then Exit Sub 'User cancelled
Loop Until IsDate(vResponse)
With Range("B2")
.NumberFormat = "mmm.dd.yyyy"
.Value = CDate(vResponse)
End With

ActiveSheet.Protect 'place at the end of the code
End Sub
****************************************************

Thanks! :eek:D
 
D

Dave Peterson

One way:

Option Explicit
Sub RequestDateFromUser()
Dim vResponse As Variant

ActiveSheet.Unprotect
On Error Resume Next
ActiveSheet.ShowAllData
On Error GoTo 0

Do
vResponse = Application.InputBox( _
Prompt:="Enter a start date." & vbCrLf & vbCrLf & _
"(By the way, Excel is pretty forgiving of the " & _
"date style you use when you enter that date.)", _
Title:="Health Log", _
Default:=Format(Date, "yyyy/mm/dd"), _
Type:=2)
If vResponse = False Then Exit Do
Loop Until IsDate(vResponse)

If vResponse = False Then
'do nothing
Else
With Range("B2")
.NumberFormat = "mmm.dd.yyyy"
.Value = CDate(vResponse)
End With
End If

ActiveSheet.Protect 'place at the end of the code
End Sub

Another way is to just do all the work at the end--after you get the date:

Option Explicit
Sub RequestDateFromUser()
Dim vResponse As Variant

Do
vResponse = Application.InputBox( _
Prompt:="Enter a start date." & vbCrLf & vbCrLf & _
"(By the way, Excel is pretty forgiving of the " & _
"date style you use when you enter that date.)", _
Title:="Health Log", _
Default:=Format(Date, "yyyy/mm/dd"), _
Type:=2)
If vResponse = False Then Exit Do 'even exit sub would work ok
Loop Until IsDate(vResponse)

If vResponse = False Then
'do nothing
Else
ActiveSheet.Unprotect 'place at the beginning of the code
On Error Resume Next
ActiveSheet.ShowAllData
On Error GoTo 0
With Range("B2")
.NumberFormat = "mmm.dd.yyyy"
.Value = CDate(vResponse)
End With
ActiveSheet.Protect 'place at the end of the code
End If
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top