Need to copy cell reference from Application.Inputbox

    I have coded a small vba Macro in the workbook named Destination.xlsx, and it is as follows:

    Code
    Sub GetDataArea()
    Dim rng As Range
    Set rng = Application.InputBox("Select the cells.", Type:=8)
End Sub

    I have selected InputBox Type:=8 because I want users to select some cells and then I want to copy the cell reference or address of those selected cells. The problem here is that the Macro is coded in Destination.xlsx, I want the user to select cells in Source.xlsx (it can be any workbook of users' choice), and copy the cell reference or cell address of Source.xlsx. It looks like the screenshot attached here >InputBox

    Basically, I want to copy the text [Source.xlsx]SheetA!$A$1:$C$10 from my Inputbox. I am not sure how do I get this. I tried rng.address but it returns only $A$1:$C$10 While need complete reference as [Source.xlsx]SheetA!$A$1:$C$10

    Pls help...

    This will do as you want


    Code
    Option Explicit
Sub GetDataArea()
Dim rng As Range
On Error Resume Next
Set rng = Application.InputBox("Select the cells.", Type:=8)
On Error GoTo 0
If rng Is Nothing Then Exit Sub
rng.Copy
MsgBox rng.Address
End Sub


    You will need to save the Destination workbook as a macro enabled file -xlsm or xlsb

    Hi royUK,


    Thanks for your reply.

    You are right about description of xlsx, xls, xlxm extentions.

    However, your prescribed solution did not work as desired. I need the user to select some cells in a worksheet other than the one where macro nis recorded. Hence, the inputbox will show the range address as [Source.xlsx]SheetA!$A$1:$C$10 which I want to copy or store in a variable, while your code displays msgbox with simple address as $A$1:$C$10 which I was anyways getting from my existing code.


    Do it like this, save macro in workbook1, run it, inputbox will appear in screen, switch to workbook2 and select some cells. It will show you the address of selected cells as [Source.xlsx]SheetA!$A$1:$C$10 and not as $A$1:$C$10.

    Is this what you want?


    Hi rouUK.

    Thanks for the quick revert.

    It still shows msgbox result as "Sheet1!$A$1". It does not show workbook name and shows only first cell of selection. It is very helpfull though. Can we add workbook name (file name) and if it can show full range selected?

    Thanks in advance for your quick help.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login