Remember selected cells in other worksheet

[Sebastian H]

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:

1. It requires the data file to be macro enabled and defeats the principle of separation of data and code.
2. It would mean that the same information is stored in two places and thus defeat another principle of good programming.
3. 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.
4. 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 ...

[arnelgp]

you can use old .xls or new .xlsb, both will save your macro.

[Sebastian H]

Update: I implemented the workaround, and in addition to the four problems listed it has the following:

5. Moving through the data list is much slower and refreshes the display on each step. (Application.ScreenUpdating = False doesn't improve that.)

So, I'd really appreciate if someone could help me with the original question.

[jolivanes]

Use Named Ranges.

[Sebastian H]

Use Named Ranges

As you can see from my OP, my workaround already uses named ranges.

One can of course use them in various ways, but I'm not aware of any that would avoid the five problems I described. If you're aware of any, can you please be more specific?

[jolivanes]

Something like this you mean?

Sub AAAAA()
Dim rngSh2 As String
rngSh2 = Selection.Address
    Sheets("Sheet1").Select
        MsgBox rngSh2
    Sheets("Sheet1").Range(rngSh2).Select
End Sub

Re: "As you can see from my OP, my workaround already uses named ranges." Where?

BTW, you mention changing from an .xlsx extension to an .xlsm extension.
If you have code in a workbook it needs to have an .xlsm extension if you want to use a macro.

[jolivanes]

Received a PM from Sebastian H.

Hello jolivanes,


In the forum you write
Quote Originally Posted by jolivanes View Post
Re: "As you can see from my OP, my workaround already uses named ranges." Where?
.


Now I'm surprised to see such a question from a VBAX Expert: Obviosly, each line of code in the Worksheet_Deactivate() sub uses a named range. I have no interest in writing that publicly and make you look stupid, so I decided to write to you privately. You may want to remove that without anyone making a fuss about it.


I also don't find your remark about macro enabled workbooks helpful. Of course, making a workbook macro enabled means that the file extension changes; I don't think any of us knows a different way to do that. As far as I know, the two mean just the same. So, I don't see how your remark is of any help. Worse, it completely ignores the the five problems I listed, and worst of all, it only further distracts from the question for which I started the thread.


Since I haven't completely given up hope that someone who actually understands the problem might be able to help solve it, and since I fear that your reply means that many who look for unanswered threads will not see it, it would be best if I either replied there or if you removed your post, if that's possible.


Kind regards,
Sebastian

Sebastian.
Don't hide your feelings by going though a PM. There is always other people that learn from our suggestions if these suggestions are right or wrong.
As I mentioned in the reply to your PM, you'll have to contact a Moderator to delete my post(s).
As far as "make you look stupid" is concerned, don't worry about that, other people have tried that and failed. After all we've been around a few years.

[p45cal]

You could try something along the lines of:
Selection.name = …
or:
Target.name = …
either in a workbook event handler of the data files (so they'd all have to be xlsm files) such as:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Selection.Name = Sh.Name & "_Selection"
End Sub

which might be vba overactive but at least it wouldn't need to be in coded in every sheet, or:
without an event handler using a line such as:
Selection.Name = ActiveSheet.Name & "_Selection"
included in a sub in a regular code-module of your code.xlsm file. Be aware that it seems to add names to the data workbook so references to that name in code.xlsm might need the workbook to be qualified too.