Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Execute GoTo Label more than once!! (Excel 2000)

    Hi,
    I am finding that a label is not running more than once. In the code:
    <pre>Option Explicit

    Sub ProtectSheets()
    Dim mySheet As Worksheet
    For Each mySheet In Worksheets
    mySheet.Protect "Password", True, True, True
    Next mySheet
    End Sub

    Sub UnProtectSheets()
    Dim mySheet As Worksheet
    Dim PW as String
    Continue:
    On Error GoTo EH
    PW = InputBox("Supply the password to unprotect all the sheets!")
    For Each mySheet In Worksheets
    mySheet.Unprotect PW
    Next mySheet
    EH:
    MsgBox Err.Description, vbCritical
    GoTo Continue
    End Sub
    </pre>

    I wnat the msgbox to run the Err.Description continually if the user does not enter the right password. But at this moment it shows the Err description once, and on a second incorrect password it runs the DEBUG msg. Why is the Error Handler not working the second, third.... time around. I do not want the system error to appear!!!

    Any help!
    Tx
    Regards,
    Rudi

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Execute GoTo Label more than once!! (Excel 2000)

    An error handler MUST be exited using a resume statement, otherwise VBA looses track of things.

    So replace

    goto continue

    with

    resume continue.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Execute GoTo Label more than once!! (Excel 2000)

    Further to your code: you should add an Exit Sub before the EH: label to avoid running through the error handler code when the sub reaches its end.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Execute GoTo Label more than once!! (Excel 2000)

    - Use Resume instead of GoTo. Resume clears the current error, so that the user starts with a clear slate.
    - You need a line Exit Sub immediately above the label.
    - You also need to give the user a way out.

    For example:

    Sub UnProtectSheets()
    Dim mySheet As Worksheet
    Dim PW As String
    Continue:
    On Error GoTo EH
    PW = InputBox("Supply the password to unprotect all the sheets!")
    If PW = "" Then Exit Sub
    For Each mySheet In Worksheets
    mySheet.Unprotect PW
    Next mySheet
    Exit Sub
    EH:
    MsgBox Err.Description, vbCritical
    Resume Continue
    End Sub

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Execute GoTo Label more than once!! (Excel 2000)

    Yes...that is working now!

    I notice I forgot the Exit Sub statement.....I got into a bit of a loop there!!! (I'll edit my code in the previous post!!)

    Thx Pieterse!
    Regards,
    Rudi

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Execute GoTo Label more than once!! (Excel 2000)

    Thanx for the explanation Hans. I was gonna ask about the Resume statement. At one stage I even had On Error GoTo 0 to cancel the error number but even this did not work. I somehow gathered that the error was not over, but even GoTo 0 did not reset it! OK...so to resume code from an Error Handler, you MUST use RESUME (Label).... This is a new tip... thanx.

    Hans...thx for the revised code. After I changed GoTo to Resume...I picked up those issues. Exit Sub is in and I added an escape on the Cancel!!

    Cheers
    Regards,
    Rudi

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Execute GoTo Label more than once!! (Excel 2000)

    Or:

    Resume
    (retries the offending statement)

    Resume Next
    (executes the statement immediately below the offending statement)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Execute GoTo Label more than once!! (Excel 2000)

    Can you: Resume Line 6? (6 being a line, say, 3 lines from the error)?
    Regards,
    Rudi

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Execute GoTo Label more than once!! (Excel 2000)

    The only options are

    Resume
    Resume Next
    Resume SomeLabel

    where SomeLabel is a label defined in the code. There is no option to resume 3 lines below the place where the error occurred, or something like that.

  10. #10
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Execute GoTo Label more than once!! (Excel 2000)

    Thx...I know that was a real way out chance I was taking!
    Have a great day further!
    Regards,
    Rudi

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Execute GoTo Label more than once!! (Excel 2000)

    From the help file:

    <hr>Resume line Execution resumes at line specified in the required line argument. The line argument is a line label or <font color=red>line number</font color=red> and must be in the same procedure as the error handler. <hr>
    Legare Coleman

  12. #12
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Execute GoTo Label more than once!! (Excel 2000)

    OK Legare, now you are throwing a spanner into the works here! Hans cleared it up for me when he said only 3 ways allowed. What line number is this you are refering to. Can I say Resume 6 (where 6 is the line to resume at??) I am probably mistaken...so please explain!!
    Regards,
    Rudi

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Execute GoTo Label more than once!! (Excel 2000)

    You can use

    Resume 6

    but if you want it 3 lines past the error you must know what line the error is in so you know (ahead of time what line number to go to)

    Also line 6 must be numbered (it is the only 1 that has to be)
    6 'This is line 6

    So whether it is a line number or a label it must be "labeled" ahead of time. It can not be a variable.

    Steve

  14. #14
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Execute GoTo Label more than once!! (Excel 2000)

    OK...this is not really the way to go. It messes up the code and I can see that it has no real benefit. Labels are clearer and easier to read! (I guess this came along from the old mainframe line code numbering from years back, and just has not died out yet!!)
    Tx Steve! and Legare!
    Regards,
    Rudi

  15. #15
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Execute GoTo Label more than once!! (Excel 2000)

    You would have to put line numbers on the lines in the procedure. The following works for me:

    <code>
    Public Sub Test()
    5 Dim I As Integer
    10 On Error GoTo 1005
    15 I = 32767
    20 I = I + 10
    25 I = I + 10
    30 Exit Sub
    1005 I = 0
    1010 Resume 25

    End Sub
    </code>

    You should also NOT have a line numbered 0 (zero).
    Legare Coleman

Page 1 of 2 12 LastLast

Posting Permissions

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