Results 1 to 3 of 3
  1. #1
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    VBA Error Handling

    I have an Excel 2003 workbook that keeps quarterly records, one week on each sheet. When a week is over, I lock that worksheet so that no more changes can be made.

    Each of the weekly sheets contains several form buttons that run simple macros to take the cursor to various cells, like this:

    Sub ShowAGames()
    ' ShowAGames Macro
    ' On a weekly page, moves the cursor to show the Tuesday and Wednesday games
    '
    On Error Resume Next
    Application.Goto Reference:="AGames"
    End Sub

    The On Error Resume Next prevents a VBA error message from appearing when the button is pressed on a locked worksheet.

    I'd like to replace it with an On Error Goto statement that shows a message box that says "Sheet is locked!" or something similar, giving me an OK button do dismiss the message box.

    How can I get that message box?
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You could simply add a line
    Code:
    Sub ShowAGames()
    ' ShowAGames Macro
    ' On a weekly page, moves the cursor to show the Tuesday and Wednesday games
    '
    On Error Resume Next
    Application.Goto Reference:="AGames"
    If Err.Number <> 0 then Msgbox "Sheet is locked", vbOKonly
    End Sub
    Regards,
    Rory

    Microsoft MVP - Excel

  3. The Following User Says Thank You to rory For This Useful Post:

    Lou Sander (2014-10-22)

  4. #3
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    That did it! I used this, which put a triangle and exclamation point in the MsgBox:

    Sub ShowAGames()
    ' ShowAGames Macro
    ' On a weekly page, moves the cursor to show the Tuesday and Wednesday games
    '
    On Error Resume Next
    Application.Goto Reference:="AGames"
    If Err.Number <> 0 Then MsgBox "Sheet is locked!", vbExclamation
    End Sub
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

Posting Permissions

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