petrfox
06-01-2012, 02:11 AM
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:
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
4. Put the code into all sheet modules (Microsoft Excel Objects - List1, Microsoft Excel Objects - List2, etc. ):
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
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:
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
You can get active cell in all sheet for example :
Sheets("List1").curcell
or List1.curcell
(it is the Range object)
1. Insert a new class module into VBA Project
2. Rename this module to MyAppEvents
3. Put the code into module:
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
4. Put the code into all sheet modules (Microsoft Excel Objects - List1, Microsoft Excel Objects - List2, etc. ):
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
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:
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
You can get active cell in all sheet for example :
Sheets("List1").curcell
or List1.curcell
(it is the Range object)