Error Handling Question

[Craw]

Greetings,
I have a code linked to a command button and I get get the Error Handling to work properly... Here is the code I have so far;

Private Sub CommandButton3_Click()
Dim rng As Range
Set rng = ActiveSheet.AutoFilter.Range
On Error GoTo ErrMsg

MsgBox rng.Columns(1). _
SpecialCells(xlCellTypeVisible).Count - 1 _
& " of " & rng _
.Rows.Count - 1 & " Records"


ErrMsg:
MsgBox ("Type in your message here."), , "MESSAGE TITLE"
End Sub


The error usually occurs at "Set rng = ActiveSheet.AutoFilter.Range" and only occurs when the active cell is not in the active table.

 

[Jon von der Heyden]

If the error occurs on setting the range object variable, and you wish to surpress this error, then you need to call your 'On Error' statement before you set the range object variable.

On Error GoTo ErrMsg
Set rng = ActiveSheet.AutoFilter.Range

 

[Craw]

Excellent thank you, however now my error msg box pops up even if there is no error...

 

[Jonmo1]

Add an Exit Sub line BEFORE the ErrMsg: line

Exit Sub
ErrMsg:
MsgBox ("Type in your message here."), , "MESSAGE TITLE"
End Sub

 

[Jon von der Heyden]

Call Exit Sub before your error handle label.

 

[Craw]

of course, thanks much for the prompt reply it worked like a charm.

Craw

 

[Jonmo1]

Although, I prefer to avoid using GoTo Statements.
They tend to create what is referred to as "spaghetti code"
It becomes difficult to read and follow the code when it's used alot.

Here is another approach.
Ignore the error with On Error Resume Next
Then test if rng has been assigned a range..

Try

Private Sub CommandButton3_Click()
Dim rng As Range
 
On Error Resume Next
Set rng = ActiveSheet.AutoFilter.Range
On Error GoTo 0
 
If rng Is Nothing Then
    MsgBox ("Type in your message here."), , "MESSAGE TITLE"
Else
    MsgBox rng.Columns(1).SpecialCells(xlCellTypeVisible).Count _
    - 1 & " of " & rng.Rows.Count - 1 & " Records"
End If
End Sub

Hope that helps.