Capturing what tab was selected

[MicroMain]

I have a macro that is invoked when leaving a tab (deactivate), to
clear off some data and do some other functions on that sheet, before
moving to other sheets.

I want to capture the tab that was selected, and then at the end of the
macro, auto select the sheet that they clicked.

Can someone please give me the command for capturing the clicked sheet?

Thanks

 

[Ken Johnson]

Is that Rick?
ActiveSheet.Name returns the name of the worksheet whose tab was
clicked, deactivating the Sheet with the Worksheet_deactivate code.
Ken Johnson

 

[Ken Johnson]

Your code must be reactivating the original sheet, otherwise it would
automatically end up on the sheet whose tab was clicked.
Here's a bit of code residing in the sheet1 module that places the
clicked sheet's name in a string variable (strClickedSht), throws up a
MsgBox with the clicked sheet's name, reactivates Sheet1, selects A1 on
Sheet1 then activates the Sheet whose tab was clicked.

Private Sub Worksheet_Deactivate()
Dim strClickedSht As String
strClickedSht = ActiveSheet.Name
MsgBox ActiveSheet.Name
Sheet1.Activate
Sheet1.Range("A1").Select
Application.EnableEvents = False
Worksheets(strClickedSht).Activate
Application.EnableEvents = True
End Sub

Ken Johnson

 

[MicroMain]

Thanks again Ken,

I've put the code (without the Msg box stuff) into the macro and it
works Marvelously.

Thanks

You are 'da Man

 

[Ken Johnson]

Hi Rick,
That's good news.
Keep up the good work.
Ken Johnson