application inputbox - selecting a different workbook/sheet

[ebotman]

Hi,
I would like to create a macro that retrieves data from workbooks and worksheets whose names have not yet been defined.

Does anyone know if it is possible to use the application.inputbox method to manually select a certain workbook

What I have now is:

Set RefCell = Application.InputBox("select one single cell", "SELECT", Default:=ActiveCell.Address(external:=True), Type:=8)
RefCell.Select

<refcell.select> fails for some reason.

Many thanks for your help,
Emiel

 

[BrianB]

We cannot Select in another worksheet unless we first use Activate.

I suggest you instead use

Application.Goto Reference:=

 

[ebotman]

thanks!
This works to some extent. I can now select a different worksheet in the same workbook. I need yet to investigate how to refer to different workbooks
Best regards,
Emiel

 

[BrianB]

Just add the workbook name to the reference.
Workbooks("Book1.xls").Worksheets("Sheet1").Range("A1")

 

[ebotman]

Thanks for the help, but I think it has something to do with the application.inputbox method which does not allow me to make a link to a different workbook. With the goto method (see below) that you suggested I can make a link to a worksheet within the same workbook. Anyways I am already pretty satisfied, but of course i would welcome any other idea. thanks
Emiel

Set RefCell = Application.InputBox("select one single cell within the ZFR table", "SELECT", Default:=ActiveCell.Address(external:=True), Type:=8)
Application.Goto Reference:=RefCell

 

[BrianB]

The trouble is that there are so many ways of doing this. I am not clear as to exactly what you are trying to do, because we are currently selecting a cell that is already selected.

Hopefully this code makes things clearer. You may have to change some of the strings to get what you want.

Sub test()
    Dim wb As String
    Dim ws As String
    Dim Refcell As String
    '----------------------
    Refcell = Application.InputBox("select one single cell within the ZFR table", _
        "SELECT", Default:=ActiveCell.Address)
    wb = ActiveWorkbook.Name
    ws = ActiveSheet.Name
    '-
    Application.Goto reference:=Workbooks(wb).Worksheets(ws).Range(Refcell)
End Sub