Clicked Excel Sheet

Hi

I have a workbook in which 7 worksheets are there. I am in sheet1 and I click some other sheet. Then in the Deactivate function of Sheet1 I have some conditions . If all the conditions are satisfied then I want to move to the other sheet whichever is clicked. For this I want to know which sheet has been clicked.

Anyone help me how to get this clicked sheet? Thanks in advance

Hi Ban,

If you trap the Workbook_SheetDeactivate event in the ThisWorkbook class module, you will see that the Sh parameter gives you the sheet you deactivated and the activesheet property gives you the other sheet you clicked, ie:


Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
MsgBox Sh.Name
MsgBox ActiveSheet.Name
End Sub

Private Sub Worksheet_Deactivate()
Debug.Print Me.Name
Debug.Print ActiveSheet.Name
End Sub

also does the trick. I find Colin's suggestion cleaner, though (inter-sheet code should be workbook code).

Private Sub Worksheet_Deactivate()
Debug.Print Me.Name
Debug.Print ActiveSheet.Name
End Sub

also does the trick. I find Colin's suggestion cleaner, though (inter-sheet code should be workbook code).

Yes, in terms of the question the point is the same though, isn't it? To get the sheet that was activated, use ActiveSheet...

Yes, in terms of the question the point is the same though, isn't it? To get the sheet that was activated, use ActiveSheet...
I dunno, I don't necessarily find it intuitive that both events should fire after the target sheet is activated. The workbook event would be equally functional if it fired before and passed the target sheet, ActiveSheet would then be the origin sheet. The parameter-less sheet event would be less useful if it worked that way, though. :)