PDA

View Full Version : Autorun Macro/Select Tab



phiore
03-09-2006, 08:00 PM
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.

malik641
03-09-2006, 08:08 PM
Sure:

Option Explicit

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
MsgBox "Hey"
End Sub

But I'm not sure about the "before sheet appears" portion :think:

smc2911
03-09-2006, 08:33 PM
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:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Application.EnableEvents = False
Worksheets("SomeSheet").Activate
MsgBox "Hey"
Sh.Activate
Application.EnableEvents = True
End Sub


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.

malik641
03-09-2006, 09:04 PM
Similar to Sean's, but this keeps the previous sheet visible...although this too gets the flickering:

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
Now how to get rid of the flickering??? :think:

phiore
03-10-2006, 03:57 AM
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.

Bob Phillips
03-10-2006, 04:13 AM
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


Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
MsgBox "Previous sheet name is " & Sh.name
MsgBox "New sheet name is " & ActiveSheet.name
End Sub

mdmackillop
03-10-2006, 04:54 AM
Now how to get rid of the flickering??? :think:

Application.ScreenUpdating = False/True?

phiore
03-10-2006, 07:00 AM
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

malik641
03-10-2006, 07:45 AM
Application.ScreenUpdating = False/True?
Can't get it to work...:think:

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 :dunno

smc2911
03-10-2006, 08:01 PM
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