Solved: Toggling Display gridlines:

[xluser2007]

Hi All,

I wanted to have code that would Toggle the Gridlines in the Activewindow.

The following code works well for this:

[vba]Option Explicit

Sub Toggle_Gridlines_Activewindow()

ActiveWindow.DisplayGridlines = Not ActiveWindow.DisplayGridlines

End Sub[/vba]
I wanted to add in some error handling in case soemthing unexpected happened, as follows:

[vba]Option Explicit

Sub Toggle_Gridlines_Activewindow()

On Error GoTo error_handler:

ActiveWindow.DisplayGridlines = Not ActiveWindow.DisplayGridlines

error_handler:

MsgBox "Error occurred when toggling gridlines in the Active Window"

End Sub[/vba]
For some reason, even though there is no error occurring when toggling, the macro still passses through to the error_handler i.e. the Msgbox is always displaye even though the toggle works without error. Could anyone please clarify what I am doing incorrectly above?

regards,

[TrippyTom]

I'm not sure why you would want to bother with error checking. This is what I use:
[VBA]
Sub ToggleMyGridlines()
If ActiveWindow.DisplayGridlines = True Then
ActiveWindow.DisplayGridlines = False
Else: ActiveWindow.DisplayGridlines = True
End If
End Sub
[/VBA]

[xluser2007]

Trippytom,

Thanks for your response.

yes I agree that Error handling is overkill for this task. But I am learning and just using it for simple stuff like this where I can to improve my skill and understanding.

Also your solution does work well, but there is logically no difference between your If Then Else and using the "NOT" operator in the first (commonly used) appraoch that I put up, correct?

In effect there is no error handling in the code you've used as well. So still wondering why it picks up the error_handler in my second suggestion. Again, please note that this is more a conceptual query than a practical one.

Thanks again.

[TrippyTom]

To fix yours, I think you would need to add a line:

[VBA]

Option Explicit
Sub Toggle_Gridlines_Activewindow()
On Error Goto error_handler:
ActiveWindow.DisplayGridlines = Not ActiveWindow.DisplayGridlines
Exit sub
error_handler:
MsgBox "Error occurred when toggling gridlines in the Active Window"
End Sub

[/VBA]
Other wise it will just continue processing your commands and since your msgbox is below the other stuff, it always fires.

[xluser2007]

TrippyTom,

Thanks for your help mate. Your solution works.

It conceptually makes sense now, if there is no error, VBA should be directed away from teh error handler before the Sub finishes.

I'll mark this thread solved.

Cheers and thanks.

[Bob Phillips]

Slightly better as a principle

[vba]

Sub Toggle_Gridlines_Activewindow()
On Error GoTo Toggle_Gridlines_Activewindow_error:
ActiveWindow.DisplayGridlines = Not ActiveWindow.DisplayGridlines

Toggle_Gridlines_Activewindow_exit:
'tidy-up code, such as reset events
Exit Sub

Toggle_Gridlines_Activewindow_error:
MsgBox "Error occurred when toggling gridlines in the Active Window"
Resume Toggle_Gridlines_Activewindow_exit
End Sub
[/vba]

[xluser2007]

Bob, always value your input and help for my learning process.

This seems like a very rigorous way to do the task. It seems particularly suited to event driven code if the toggling of gridlines was required in such a case. (I should start to look into event driven code in more detail when learning automation).

Thanks again.

[Bob Phillips]

I don't think it is peculiar to event driven code, it is more a error handling strategy that you can apply overall.

I use a very similar approach in all my 'real' code, a pass-back error handling process that only gets reported at the entry level procedure. Something like this

[vba]

Sub Main()

Const ProcName as String = "Main"

On Error Goto Main_Error

'some code

If Not MyFirstCall Then Err.Raise AppErrorNum

'some more code

If Not MySecondCall Then Err.Raise AppErrorNum
'etc

Main_Exit:
Exit Sub

Main_Error:
If ErrorMsg = "" Then
ErrorMsg = "Error in " & ProcName & vbNewLine & _
"Error: " & Err.Number & ", " & Err.Description
End If
MsgBox ErrorMsg
Resume Main_Exit
End Sub

Public Sub MyFirstCall() As Boolean

Const ProcName as String = "MyFirstCall "
MyFirstCall = True
On Error Goto MyFirstCall_Error

' the real code

MyFirstCall_Exit:
'tidy-up code
Exit Function

MyFirstCall_Error:
MyFirstCall = False
ErrorMsg = "Error in " & ProcName & vbNewLine & _
"Error: " & Err.Number & ", " & Err.Description
Resume MyFirstCall_Exit
End Function

Public Sub MySecondCall() As Boolean

Const ProcName as String = "MySecondCall"
MySecondCall = True
On Error Goto MySecondCall_Error

' the real code, including

If Not MyThirdCall Then Err.Raise AppErrorNum

MySecondCall_Exit:
'tidy-up code
Exit Function

MySecondCall_Error:
MySecondCall = False
ErrorMsg = "Error in " & ProcName & vbNewLine & _
"Error: " & Err.Number & ", " & Err.Description
Resume MySecondCall_Exit
End Function

Public Sub MyThirdCall() As Boolean

Const ProcName as String = "MyThirdCall"
MyThirdCall = True
On Error Goto MyThirdCall_Error

' the real code

MyThirdCall_Exit:
'tidy-up code
Exit Function

MyThirdCall_Error:
MyThirdCall= False
ErrorMsg = "Error in " & ProcName & vbNewLine & _
"Error: " & Err.Number & ", " & Err.Description
Resume MyThirdCall_Exit
End Function
[/vba]

In practice I have an error handling procedure that I call, I don't build the message in the procedures themselevs, but it shows the principle.

[xluser2007]

I don't think it is peculiar to event driven code, it is more a error handling strategy that you can apply overall.

I use a very similar approach in all my 'real' code, a pass-back error handling process that only gets reported at the entry level procedure. Something like this

[vba]

Sub Main()

Const ProcName as String = "Main"

On Error Goto Main_Error

'some code

If Not MyFirstCall Then Err.Raise AppErrorNum

'some more code

If Not MySecondCall Then Err.Raise AppErrorNum
'etc

Main_Exit:
Exit Sub

Main_Error:
If ErrorMsg = "" Then
ErrorMsg = "Error in " & ProcName & vbNewLine & _
"Error: " & Err.Number & ", " & Err.Description
End If
MsgBox ErrorMsg
Resume Main_Exit
End Sub

Public Sub MyFirstCall() As Boolean

Const ProcName as String = "MyFirstCall "
MyFirstCall = True
On Error Goto MyFirstCall_Error

' the real code

MyFirstCall_Exit:
'tidy-up code
Exit Function

MyFirstCall_Error:
MyFirstCall = False
ErrorMsg = "Error in " & ProcName & vbNewLine & _
"Error: " & Err.Number & ", " & Err.Description
Resume MyFirstCall_Exit
End Function

Public Sub MySecondCall() As Boolean

Const ProcName as String = "MySecondCall"
MySecondCall = True
On Error Goto MySecondCall_Error

' the real code, including

If Not MyThirdCall Then Err.Raise AppErrorNum

MySecondCall_Exit:
'tidy-up code
Exit Function

MySecondCall_Error:
MySecondCall = False
ErrorMsg = "Error in " & ProcName & vbNewLine & _
"Error: " & Err.Number & ", " & Err.Description
Resume MySecondCall_Exit
End Function

Public Sub MyThirdCall() As Boolean

Const ProcName as String = "MyThirdCall"
MyThirdCall = True
On Error Goto MyThirdCall_Error

' the real code

MyThirdCall_Exit:
'tidy-up code
Exit Function

MyThirdCall_Error:
MyThirdCall= False
ErrorMsg = "Error in " & ProcName & vbNewLine & _
"Error: " & Err.Number & ", " & Err.Description
Resume MyThirdCall_Exit
End Function
[/vba]
In practice I have an error handling procedure that I call, I don't build the message in the procedures themselevs, but it shows the principle.

There is a fair bit in there for me to digest Bob .

I'll think it through properly and come back to you with more queries.

thanks again.