On Error GoTo

[andy_uk]

Hi all ; sorry to keep going on, then again it's all good traffic.

I have some code on a custom button, on a custom toolbar, to display a UserForm with the 3rd tab of its multipage to the fore (MultiPage1.Value = 2).

The user will be told that this button only applies when the active sheet is called whatever AND row > 2 and column = 5 or 6 AND cell <> "".

Obviously I can write these conditions in 1 by 1, to stop the form displaying, e.g.

If ActiveSheet.Name <> "Whatever" then Exit Sub
If ActiveCell.Row < 3 Then Exit Sub

, etc., etc., thus nothing happens. But I want to then show a MsgBox ("Selection out of range" or something) so that the user doesn't just forget and/or assume the button has stopped working.

So far I have the following:

Sub Test()
On Error GoTo WrongRange
If ActiveCell.Row < 3 Then Exit Sub 'for example
frmQuote.MultiPage1.Value = 2
frmQuote.Show
Exit Sub
WrongRange:
MsgBox "Active cell must be in row 3."
End Sub

This is adapted from a book by a VERY famous author and, err, & it's not working. So I must be missing something. BTW, "Break on Unhandled Errors" is ON.

TIA,
Andy

[roos01]

perhaps instead of using exit sub try the goto like:

Sub Test22()
On Error GoTo WrongRange
If ActiveCell.Row < 3 Then
frmQuote.MultiPage1.Value = 2
frmQuote.Show
Exit Sub
Else
GoTo WrongRange 'for example
End If
WrongRange:
MsgBox "Active cell must be in row 3."
End Sub

Cheers!
Jeroen

[TonyJollans]

Hi andy,

First of all, I want my title back - guess I'll have to work for it

Now, there is no error in your code - that is, no run time error will occur. The error trap has no way of knowing what your application criteria are and what you consider to be an error; it only traps errors where it can't do what you ask. Here, it has no trouble doing what you ask - either exit directly if the current row is less than 3, or switch your multipage and then exit.

Jeroen's code will work, but another way to code what you want might be to use a Case construct ..

Select Case Treu 
Case ActiveSheet.Name <> "Whatever" 
Case ActiveCell.Row < 3 
    MsgBox "Active cell must be in row 3 of sheet Whatever." 
    Exit Sub
Case Else
    frmQuote.MultiPage1.Value = 2 
    frmQuote.Show 
End Select

[andy_uk]

Sorry Tony ; if Anne can xfer my score to TSG & wipe it from here, that'll be fine. Check this link if you dare http://jongy.tripod.com/History.html#billy

What I wanted was to lay down several conditions (correct sheet must be active AND row must be > 2 AND column must be E or F AND cell mustn't be blank), without getting into ?-a-dozen If ... Then ... ElseIf loops.

Aside ; logically you'd think you oughta be able to say

If X Or If Y Or If Z Then
Do something
Else
Do something else ; guess not. :no

I couldn't get Select Case to check multiple lines before the Exit Sub, in the end it (?surprisingly?) played ball with

Select Case True
Case ActiveSheet.Name <> "Orders", ActiveCell = "", ActiveCell.Row < 3, _
ActiveCell.Column < 5, ActiveCell.Column > 6
    MsgBox "Active cell is outside required range."
    Exit Sub
Case Else
    frmQuote.MultiPage1.Value = 2
    frmQuote.Show
End Select

Which is pretty cool :cool (maybe I discovered something by accident?)

Thanks for the help, guys.

Rgds,
Andy

[TonyJollans]

Sorry, Andy, you're absolutely right about the syntax - I really should do things in the VBE before posting them off the top of my head. Glad you sorted it anyway.

[johnske]

Aside ; logically you'd think you oughta be able to say

If X Or If Y Or If Z Then
Do something
Else
Do something else ; guess not. :no

Rgds,
Andy

Logically there is a way to do this andy, you simply had too many "IF"s in your expression above. This simple example worked fine for me....
(sorry, much too late at nite, I actually had to EDIT this )

Sub IfOr()
X = Range("A1")
If X = 3 Or X = 4 Or X = 5 Then Range("A2") = X
If X < 3 Or X > 5 Then Range("A2") = "11"
End Sub