application inputbox - selecting a different workbook/sheet

ebotman

Board Regular
Joined
Jun 24, 2004
Messages
89
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
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
We cannot Select in another worksheet unless we first use Activate.

I suggest you instead use

Code:
Application.Goto Reference:=
 
Upvote 0
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
 
Upvote 0
Just add the workbook name to the reference.
Workbooks("Book1.xls").Worksheets("Sheet1").Range("A1")
 
Upvote 0
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
 
Upvote 0
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.
Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top