The active cell in a sheet other than the active sheet (VBA Excel)

[petrfox]

How can you determine what is the active cell in a sheet other than the active sheet without activate that sheet?

1. Insert a new class module into VBA Project
2. Rename this module to MyAppEvents
3. Put the code into module:

[vba]
Private WithEvents XLApp As Application

Private Sub Class_Initialize()
Set XLApp = Application
End Sub

Private Sub XLApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
'it sets curcell in a sheet module
On Error Resume Next
If Sheets(Sh.Name).curcell = "" And False Then
'this is an error - variable curcell doesn't exist
Else
On Error GoTo 0
Set Sheets(Sh.Name).curcell = Target
End If
End Sub[/vba]

4. Put the code into all sheet modules (Microsoft Excel Objects - List1, Microsoft Excel Objects - List2, etc. ):

[vba]Public curcell As Range '(have to be first, before subs and functions)

Private Sub Worksheet_Activate()
If ExcelEvents Is Nothing Then Set ExcelEvents = New EventAppClass
Set Me.curcell = ActiveCell 'first setting of curcell
End Sub
[/vba]
5. Now you can get active cells in all sheets. Try to insert a new module in Modules (Module1 for example.) and put next code into:

[vba]Public ExcelEvents As EventAppClass '(have to be first, before subs and functions)

Public Sub example ()
Dim she as worhsheet
for each she in Worksheets
msgbox "Active cell in sheet: " & Sheets(she.name) & " is " Sheets(she.Name).curcell.Address
next
end sub
[/vba]
You can get active cell in all sheet for example :
Sheets("List1").curcell
or List1.curcell
(it is the Range object)

[Jan Karel Pieterse]

Seems to me the class module isn't needed at all?