MACRO HELP!!

rebelhunter

New Member
Joined
Oct 24, 2002
Messages
11
I would like to create a dropdownlistbox on an excel spreadsheet, to
enable users to select a filename,
which will then automatically find the excel report selected by the
user. I would then like the filenames
within the formulas on the spreadhseet with the listbox to change to the filename
selected by the user so the formulas derive
the figures from the correct excel report - i've been trying to write a macro to do this, but keep on getting 'syntax errors' - HELP PLEASE??
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
first I would use a combo box for your drop down and then link the value selected to a cell (say D1) - this should give you something like "Book1.xls"

Then - your formulas should use INDIRECT and refer to the linked cell (D1)

=INDIRECT("["&D1&"]Sheet1!$A$1")

(note the above uses XP so will be a little different to the code for looking into another file from Excel 2000 (which uses ' etc...)

As for activating the Macro from the selection - then you need an onactivate type thing - not my strong point. Hopefully one of the others can let you know.
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
For those with similar aim:

ComboBox created with linked cell (C1) - used ComboBox Change to open selected workbook and then INDIRECT within worksheet for formulas using C1 as reference

Formula ex:

=INDIRECT("["&C1&".xls]"&"Sheet1!A1")

Macro for Combo Box:

Private Sub ComboBox1_Change()

Workbooks.Open ("O:Mr_excel" & Sheets("sheet1").Range("c1") & ".xls")
Windows("book2.xls").Activate

CORRECTFILE = MsgBox("Have You Opened the Correct File?", vbYesNo, "CORRECT FILE?")

If CORRECTFILE = vbYes Then Exit Sub

Windows(Sheets("SHEET1").Range("C1") & ".XLS").Activate
ActiveWorkbook.Close SAVECHANGES = True

MsgBox "Please Select Another File", vbInformation, "Selection"

End Sub
 

Forum statistics

Threads
1,144,148
Messages
5,722,784
Members
422,457
Latest member
Mrmuskins

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
Top