If Sheet Is Selected

MattLeek · March 24th, 2004, 10:26 AM

Hi, its me again!

I dont know much Excel VBA but i am getting through by Reading Macros. But i now need to display a message when the user selects a sheet. I've go as far as getting a Private Sub up for when sheets are selected but im stuck on how to see which sheet it is. There is an argument "ByVal Target As Range" but thats about it.

Sorry to keep bothering this forum!
Thanks

Matthew Leek

pgtips · March 24th, 2004, 11:49 AM

Try using the Worksheet_Activate event. Just open up the VB macro editor and from the project explorer, choose the sheet for which you want to display the message, and paste in a sub like this:

Code:

Private Sub Worksheet_Activate()
    MsgBox "hello from " & Me.Name
End Sub

BTW its no bother - ask as many questions as you like.

rgds
Phil

MattLeek · March 24th, 2004, 01:03 PM

Thanks for the help,

and ill keep the question thing in mind! :D

Matthew Leek

MattLeek · March 25th, 2004, 12:53 PM

Hi again, just one more thing i promise! How would i find out what the previous sheet was?

Matthew Leek

pgtips · March 26th, 2004, 05:20 AM

I think you would have to put some code in the Worksheet_Deactivate event to store the name of the worksheet that was deactivated in a module level variable, which you can then access from the Worksheet_Activate event of the next sheet.