*Resolved* OnSheet_Selection?

[TheRobster]

Is there a VBA command (in Excel) that will run a procedure located in a sheet when that sheet is activated? I was thinking of something along the lines of the Selection_Change command but instead of having to change the focus on the sheet, I want the procedure to run when that sheet is selected.

I was rather hoping that Excel would keep a track of all the procedures in a sheet so that if the conditions of the sheet change and one of the procedures says 'do something if a cell value = a certain number' then that change would automatically occur, but it seems that I have to call the procedure to make it work. I can get it to work by attaching it to a Forms button but I want it to run whenever that sheet is selected and not only if I change the focus or press a button!

Any ideas?

Cheers
-Rob

[alex_read]

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    If (Sh.Name = "Sheet1") Then
        MsgBox "Hey, whaddayaknow Sheet1's been selected"
    End If
End Sub

Place this code inside of the "ThisWorkbook" module & enjoy!!!

[TheRobster]

That did the trick. Cheers Alex.

-Rob

*Edit* I would have thought that placing a subroutine within a sheet would mean that Excel either checks the subroutine to see if any conditions have been met that mean it should be run, or monitors them continously so that if any conditions change which match the criteria for a subroutine then it is automatically run.

This doesn't seem to be the case though.........it seems you always have to call a subroutine, even when the conditions for it to be executed are met it doesn't run automatically. Is this correct?

[RobDog888]

Place this in the ThisWorkbook class. It will give real-time detection,
so to speak, not when the sheet changes from sheet1 to sheet2, etc.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    MsgBox "Hey, whaddayaknow " & Sh.Name & "'s been selected"
End Sub