Sebastian H
08-05-2021, 01:23 AM
When the user selects another worksheet, Excel nicely remembers what the selection in the old sheet was, so that when the user switches back to the old sheet, it selects the same range again.
Is there a way to access that information from VBA? Unfortunately, the following doesn't do the trick, since Selection is not a property of Worksheet.
Given:
data.xlsx with two worksheets – Sheet1 and Sheet2
code.xlsm with the following macro:
Sub Info()
Dim other_worksheet As Worksheet
If ActiveSheet.Name = "Sheet1" Then
Set other_worksheet = Worksheets("Sheet2")
Else
Set other_worksheet = Worksheets("Sheet1")
End If
Debug.Print "selected in the other sheet:", other_worksheet.Selection.row, other_worksheet.Selection.Column
End Sub
As a workaround, one can change data.xlsx to data.xlsm and do the remembering explicitly in each Worksheet_SelectionChange sub. But that's unsatisfactory for several reasons:
It requires the data file to be macro enabled and defeats the principle of separation of data and code.
It would mean that the same information is stored in two places and thus defeat another principle of good programming.
It has to be programmed in each sheet of each data file (yes, there are actually more than one) and thus defeat yet another principle of parsimony.
It would create much more VBA activity than necessary, since it is not possible to use Worksheet_Deactivate for this (the below code will show the selection in the newly selected sheet because it gets executed after the change) but it has instead to be remembered for each selection change.
Private Sub Worksheet_Deactivate()
[last_sel_row].FormulaR1C1 = ActiveCell.Row
[last_sel_col].FormulaR1C1 = ActiveCell.Column
Debug.Print "remembering cell:", [last_sel_row].FormulaR1C1, [last_sel_col].FormulaR1C1
End Sub
BTW, I found a similar question for Word here: macro to remember page number ... (http://www.vbaexpress.com/forum/showthread.php?57979-macro-to-remember-page-number-where-last-hyprlink-was-accessed&highlight=remember+selection)
Is there a way to access that information from VBA? Unfortunately, the following doesn't do the trick, since Selection is not a property of Worksheet.
Given:
data.xlsx with two worksheets – Sheet1 and Sheet2
code.xlsm with the following macro:
Sub Info()
Dim other_worksheet As Worksheet
If ActiveSheet.Name = "Sheet1" Then
Set other_worksheet = Worksheets("Sheet2")
Else
Set other_worksheet = Worksheets("Sheet1")
End If
Debug.Print "selected in the other sheet:", other_worksheet.Selection.row, other_worksheet.Selection.Column
End Sub
As a workaround, one can change data.xlsx to data.xlsm and do the remembering explicitly in each Worksheet_SelectionChange sub. But that's unsatisfactory for several reasons:
It requires the data file to be macro enabled and defeats the principle of separation of data and code.
It would mean that the same information is stored in two places and thus defeat another principle of good programming.
It has to be programmed in each sheet of each data file (yes, there are actually more than one) and thus defeat yet another principle of parsimony.
It would create much more VBA activity than necessary, since it is not possible to use Worksheet_Deactivate for this (the below code will show the selection in the newly selected sheet because it gets executed after the change) but it has instead to be remembered for each selection change.
Private Sub Worksheet_Deactivate()
[last_sel_row].FormulaR1C1 = ActiveCell.Row
[last_sel_col].FormulaR1C1 = ActiveCell.Column
Debug.Print "remembering cell:", [last_sel_row].FormulaR1C1, [last_sel_col].FormulaR1C1
End Sub
BTW, I found a similar question for Word here: macro to remember page number ... (http://www.vbaexpress.com/forum/showthread.php?57979-macro-to-remember-page-number-where-last-hyprlink-was-accessed&highlight=remember+selection)