PDA

View Full Version : Solved: Activating a procedure if a worksheet tab is clicked



zagrijs
12-06-2012, 03:44 AM
:hi:
Is there a way to detect when another worksheet tab is clicked and to activate a procedure when it is done?

: pray2: :doh:

GTO
12-06-2012, 04:55 AM
Yes...

In the specific worksheet's module: Worksheet_Activate
In the workbook's module: Workbook_SheetActivate

Could you tell us if this is to only be 'fired/run' in the specific workbook?

Mark

Trebor76
12-06-2012, 04:56 AM
Yes - follow these four steps:

1. Right click on the relevant tab
2. From the shortcut menu select View Code
3. Copy and paste the following code into the worksheet event module:


Option Explicit
Private Sub Worksheet_Activate()
MsgBox "I've just been selected."
End Sub

4. From the File menu select Close and Return to Microsoft Excel

Now each time the tab in step 1 is selected the message box in my code will appear. When you're ready put your code in it's place.

Regards,

Robert

p45cal
12-06-2012, 04:59 AM
If you right-click on a sheet's tab, choose View code…, then in the left dropdown at the top of the code window, choose Worksheet, then in the right drop down choose Activate (or Deactivate), code in these procedures will run automatically on clicking the sheet's tab, or clicking away from a sheet's tab in the case of Deactivate.

zagrijs
12-07-2012, 02:22 AM
Thank you to all three replies. Pretty straight forward and simple. :clap2: :friends: :thumb