Consulting

Results 1 to 6 of 6

Thread: On Error GoTo

  1. #1
    VBAX Regular
    Joined
    Jul 2004
    Location
    Cambridge, England
    Posts
    23
    Location

    On Error GoTo

    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:

    [VBA] 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 [/VBA]

    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

  2. #2
    VBAX Regular
    Joined
    Jun 2004
    Location
    The Netherlands
    Posts
    34
    Location
    perhaps instead of using exit sub try the goto like:
    [VBA]
    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
    [/VBA]

    Cheers!
    Jeroen

  3. #3
    VBAX Master TonyJollans's Avatar
    Joined
    May 2004
    Location
    Norfolk, England
    Posts
    2,291
    Location
    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 ..

    [VBA] 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
    [/VBA]
    Enjoy,
    Tony

    ---------------------------------------------------------------
    Give a man a fish and he'll eat for a day.
    Teach him how to fish and he'll sit in a boat and drink beer all day.

    I'm (slowly) building my own site: www.WordArticles.com

  4. #4
    VBAX Regular
    Joined
    Jul 2004
    Location
    Cambridge, England
    Posts
    23
    Location
    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

    [VBA] 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
    [/VBA]

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

    Thanks for the help, guys.

    Rgds,
    Andy

  5. #5
    VBAX Master TonyJollans's Avatar
    Joined
    May 2004
    Location
    Norfolk, England
    Posts
    2,291
    Location
    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.
    Enjoy,
    Tony

    ---------------------------------------------------------------
    Give a man a fish and he'll eat for a day.
    Teach him how to fish and he'll sit in a boat and drink beer all day.

    I'm (slowly) building my own site: www.WordArticles.com

  6. #6
    Administrator
    Chat VP
    VBAX Guru johnske's Avatar
    Joined
    Jul 2004
    Location
    Townsville, Australia
    Posts
    2,872
    Location
    Quote Originally Posted by andy_uk
    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 )

    [VBA]

    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

    [/VBA]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •