Reference to selected cells in another worksheet.

[Herakles]

Selection means ActiveWorkbook.ActiveSheet.Selection to Excel

How can I make reference to the selected cells in another worksheet without activating that worksheet.

Thanks

 

[Herakles]

I can only loop through Areas or Cells in a Selection if I can make reference to that Selection.
How can I do that if the sheet is not the active sheet?

It seems that making reference to that Selection can only be made if the sheet containing that Selection is active.

 

[James006]

You can test following

Sub TestLoop()
Dim c As Range
Dim rng As Range

' Make sure to adapt the Sheet and the Range
Set rng = Sheet2.Range("B2:B10")
    For Each c In rng
        MsgBox "The value " & c & " is located in cell " & c.Address
    Next c
End Sub

 

[Herakles]

But I don't know what the range is as it is referenced by the Selection which I cannot access unless
I activate the sheet containing the Selection.

 

[James006]

What determines what you are calling the Selection ???

 

[Herakles]

The Selection is made by the User by selecting one or multiple Areas on the worksheet.

The selection has been made before the code is executed.

One way is to enable the User to select the Area or Areas whilst the code is running. The sheet will still be the
active sheet and the code can then set a range object to the Selection.

 

[James006]

The Selection is made by the User by selecting one or multiple Areas on the worksheet.

The selection has been made before the code is executed.

One way is to enable the User to select the Area or Areas whilst the code is running. The sheet will still be the
active sheet and the code can then set a range object to the Selection.

Two general comments ... since you have decided not to post your macro

1. An InputBox can allow a User to select a range ...

2. If, as you say, the Selection has been made before the code is executed ... you could have this info stored in a variable ...
and use it when needed ... without Activating anything ...

Good Luck for your project

 

[Herakles]

The code I first used to test the idea was this. This produces a 438 Object doesn't support this property or method error.

Set rng = Worksheets("Sheet1").Selection

I have to activate Sheet1 and then assign the range to the rng variable using this code.

Set rng = Selection

 

[Tupe77]

It's strange that VBA can't read the selections on different sheets, because the selections made on different worksheets are actually preserved.

As I understand it is because Selection is Application level properties, so there can only be one readable Selection at a time.
(the) "new" Selection is created every time the worksheet changes.

So the selections must be reviewed one active sheet at a time, or save it to variable when the user makes a selection.

My apologies for any quirks, English is not my native language.

 

[James006]

How to allow a User to Select a Range with an InputBox :

VBA To Allow User To Select A Range With An InputBox

In this article you will learn how to use Excel’s built-in InputBox dialog to allow your users to write-in or select a cell range. This range can then be used throughout the remainder of your executable VBA code.

www.thespreadsheetguru.com

 

[Tupe77]

the answer to the question was that it is possible to read the selected area only from the active worksheet.
I don't know your goal, so my example might be pointless and not suitable for your use.
I added it here anyway.

The Workbook_SheetSelectionChange event enables the selections made after opening the workbook to be saved in a variable.

The Workbook_Open event enables saving worksheet selections to variables.
However, this is done by activating the worksheets one by one, so I don't know if this is of any use in your case.

When opening the workbook, the subprogram reads the Selection addresses of all worksheets into the dictionary.

After opening, the changes in the Selection worksheets are automatically updated in the dictionary.


Copy these to ThisWorkbook section:

Private Sub Workbook_Open()
    Call TS_SheetsUserSelection 'Calls a subroutine called "TS_SheetsUserSelection" from the modules section
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    dict(Sh.Name) = Target.Address ' Record changes made to selections on any worksheets
End Sub

Copy these to the empty Module section:

Option Explicit
Global dict As Object 'Global (and static) variable to store selections.

Sub TS_SheetsUserSelection() ' Go through all the worksheets and save their "Selection" address in the dictionary.
On Error GoTo ErrHand: Application.EnableEvents = False: Application.Calculation = xlManual: Application.ScreenUpdating = False: Application.DisplayAlerts = False
Set dict = CreateObject("Scripting.Dictionary")
Dim iWS As Worksheet, CurrentWS As Worksheet: Set CurrentWS = ActiveSheet

For Each iWS In Worksheets
            iWS.Activate ' Activating the worksheet
            dict.Add iWS.Name, Selection.Address ' Add the worksheet name as the dictionary key and the selected range as the keys value
Next

ErrHand:
    CurrentWS.Activate ' Return to the worksheet that was open when started
    Application.EnableEvents = True: Application.Calculation = xlAutomatic: Application.ScreenUpdating = True: Application.DisplayAlerts = True: On Error GoTo 0
End Sub


Sub PrintSelections() ' Just for testing
Dim WsTMP As Worksheet
    For Each WsTMP In Worksheets
        Debug.Print WsTMP.Name & ": " & dict(WsTMP.Name) ' Print Worksheet name and selection address
    Next
End Sub


Sub Fill_SheetsUserSelection() ' An example of using a dictionary
Dim ws As Worksheet, WorkSheetsName As String, Fills As String

' the name of the worksheet whose selection is filled
WorkSheetsName = "Sheet1"
' The text with which the selected area is filled
Fills = "TestFill"

Set ws = Worksheets(WorkSheetsName)
ws.Range(dict(ws.Name)).Value = Fills
End Sub

The subroutine is not fail-safe, for example renaming and deleting a sheet will still leave the data in the dictionary.
A very bad and burdensome quick fix is to add the end of ThisWorkbook section:

Private Sub Workbook_SheetActivate(ByVal Sh As Object) ' Always go through all the worksheets when the worksheet is activated
     Call TS_SheetsUserSelection
End Sub