hi everyone
quick question please. is it possible to have a macro run automatically, when selecting a tab in a workbook? each time i select a tab, before the sheet appears, a macro runs. thanks for any help. you folks are great.
hi everyone
quick question please. is it possible to have a macro run automatically, when selecting a tab in a workbook? each time i select a tab, before the sheet appears, a macro runs. thanks for any help. you folks are great.
Sure:
[VBA]Option Explicit
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
MsgBox "Hey"
End Sub
[/VBA]
But I'm not sure about the "before sheet appears" portion
New to the forum? Check out our Introductions section to get to know some of the members here. Feel free to tell us a little about yourself as well.
The "before sheet appears" bit (if required) is a bit tricky. At first I thought using Workbook_SheetDeactivate instead would do the job, but appears to move to the new sheet before the macro call is made. You could do something like this:
[VBA]Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Application.EnableEvents = False
Worksheets("SomeSheet").Activate
MsgBox "Hey"
Sh.Activate
Application.EnableEvents = True
End Sub
[/VBA]
If you try this, you do get a brief flash of the destination sheet, but you then flick to another sheet (I've picked SomeSheet) to do your thing before returning to the selected sheet. Not perfect, but it's something.
Sean.
Similar to Sean's, but this keeps the previous sheet visible...although this too gets the flickering:
[vba]Option Explicit
Dim shName As String
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Application.EnableEvents = False
Sheets(shName).Activate
MsgBox "Hey"
Sh.Activate
Application.EnableEvents = True
End Sub
Private Sub Workbook_SheetDeActivate(ByVal Sh As Object)
shName = Sh.Name
End Sub
[/vba] Now how to get rid of the flickering???
New to the forum? Check out our Introductions section to get to know some of the members here. Feel free to tell us a little about yourself as well.
good morning. you folks are great. ill give these suggestions a shot as soon as i get into work. not worried about the flickering, just that my macro runs when i select a tab. again thanks.
I assume that the reason for wanting to run the macro before the new sheet displays is to run the macro against data on the previous sheet? If so, by using the Deactivate event, the Sh object still referes to the sheet just exited, so your code can address data there like so
[vba]
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
MsgBox "Previous sheet name is " & Sh.name
MsgBox "New sheet name is " & ActiveSheet.name
End Sub
[/vba]
Application.ScreenUpdating = False/True?Originally Posted by malik641
Now how to get rid of the flickering???
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
hi, i must be doing something wrong. i cant get any of the vba code to come back with the "hey", so i assume its not functioniing as intended. i put the code on the pane for the sheet. can someone nudge me in the right direction. thanks
Can't get it to work...
I'm going to have to get back on this later this afternoon..a little busy at my job right now.
And phiore, make sure that Application.EnableEvents = True...if the Message Box still isn't coming up....then
New to the forum? Check out our Introductions section to get to know some of the members here. Feel free to tell us a little about yourself as well.
I don't think that ScreenUpdating will do the trick: by the time you set it to False, the new sheet has already been activated. Initially I thought that SheetDeactivate might give you a chance to set ScreenUpdating to false before activating the new sheet, but it does not.
Sean
Posting Permissions
Reply With Quote
