Excel Application.Input full address

Question

I am trying to write a macro in Excel where I could format sheets dynamically. 1 macro, without a hardcoded sheet's name.

I don't know how to extract the entire value of the Application.Selection if the macro points to another sheet (and I need to do it on another sheet)

Sub GetRangeToModify()

    Dim RangeOfA1, RangeOfA2 As range
    
    Set RangeOfA1 = Application.Selection
    Set RangeOfA2 = Application.InputBox("Select the loaded CSV's sheet' A1 field", "Select A1 cell", RangeOfA1, Type:=8)
    MsgBox RangeOfA2.Address
End Sub

With this snippet you can get the location of the cell, but the sheet's name is not visible ( e.g. $K$16; not Sheet1!$K$16)

The plan is: get the full string from the selection (e.g: Sheet2!$L$19), activate another sheet with the extracted sheet's name, and do stuff there (insert new columns etc.)

Is there a way to do it properly?

FWIW, to activate the sheet, you just need `RangeOfA2.Worksheet.Activate` — chris neilsen, Dec 12 '21 at 17:58
And you probably don't need to Activate at all. [See this](https://stackoverflow.com/q/10714251/445425) — chris neilsen, Dec 12 '21 at 17:59

bugdrown · Accepted Answer · 2021-12-11T11:41:14.143

0

Try:

Dim RangeOfA1 as Range, RangeOfA2 As range 'must assign explicit data type for each declaration; otherwise, data type is Variant by default.

Dim strWS as String 'worksheet name 

Set RangeOfA1 = Application.Selection
Set RangeOfA2 = Application.InputBox("Select the loaded CSV's sheet' A1 field", "Select A1 cell", RangeOfA1, Type:=8)
 strWS = RangeOfA2.Worksheet.Name
 MsgBox "'" & strWS & "'!" & RangeOfA2.Address

edited Dec 11 '21 at 11:41

answered Dec 11 '21 at 11:35

bugdrown

333
2
11

@bugdrown Couple of hints: a) Instead of assigning `strWS = RangeOfA2.Worksheet.Name` you can refer to the range's parent via `strWS = RangeOfA2.Parent.Name`. b) If you want to include the workbook reference, too you might code `MsgBox RangeOfA2.Address(External:=True) ` showing e.g. `[Test.xlsm]Sheet1!$A$3` – T.M. Dec 11 '21 at 12:02
1

I used Range.Worksheet.Name because I thought it would be much clearer to the o.p. than Range.Parent.Name. If Selection.Parent is ever used then the parent isn't necessarily the worksheet. – bugdrown Dec 11 '21 at 12:23

Excel Application.Input full address

1 Answers1