PDA

View Full Version : Run macro whenever you change sheets



bhn5001
11-13-2009, 02:29 PM
Hi all,

I'm looking to write a macro that runs whenever I change sheets. Note, when I want it to run when I actually change sheets, not change something on the sheets. All the macro itself would do is search the sheet which you just left for cells with a certain color index and then update values in two specific cells depending on which color indexes were found. That part i can figure out, its the event handler that is giving me trouble. Is there a way to make the Workbook_Deactivate event handler apply to all sheets?

Thanks,

Brad

Bob Phillips
11-13-2009, 02:54 PM
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)

MsgBox Sh.Name & " refers to the sheet we came from"
MsgBox ActiveSheet.Name & " refers to the sheet we are on"
End Sub


This is workbook event code.
To input this code, right click on the Excel icon on the worksheet
(or next to the File menu if you maximise your workbooks),
select View Code from the menu, and paste the code

bhn5001
11-16-2009, 08:37 AM
XLD,

Thanks a lot, works great.

Brad