Worksheet message box accessed manually and using macros

[Stevew192]

Hi all,

I've got a workbook which holds lots of information on a number of worksheets. I've used shapes/macros to create buttons on sheet 1. These buttons select the appropriate worksheet and filters information to make it simple for the layman to find what they want.

Navigating manually using the worksheet tabs won't reset the filters and may make subsequent searches incomplete/fail so I've added a message box to remind the user to navigate using the buttons.

Is there anyway of identifying if the worksheet was selected using one of the macros and skipping the message box?? Ie. can we treat a manual click differently to selecting using a macro?

Thanks in advance.

[LJMetzger]

Hi Steve,

You may be able to do what you want by using a global variable to indicate whether a sheet was activated by Macro or Manually.

Try the following code in the ThisWorkbook Module:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

  If bSheetActivatedByMacro = True Then
    MsgBox "Workbook_SheetActivate() triggered BY MACRO on '" & Sh.Name & "'" & vbCrLf & _
           "No Warning message needed."
  Else
    MsgBox "Workbook_SheetActivate() triggered MANUALLY on '" & Sh.Name & "'" & vbCrLf & _
           "DANGER WILL ROBINSON."
  End If
End Sub

Try the following code in an ordinary module:

Option Explicit
Public bSheetActivatedByMacro As Boolean

Sub CommandButton1_Click()
  bSheetActivatedByMacro = True
  Sheets("Sheet2").Select
  
  '''''
  'your other code here
  '''''
  
  bSheetActivatedByMacro = False
  

End Sub

Code tested and working in Excel 2003. When a sheet is selected manually, the WARNING PATH is activated. When a sheet is selected by Macro, the NO WARNING path is activated.

Lewis

[Stevew192]

Thanks Lewis, I'm back in the office today so I'll give it a try.

Thanks again... Steve