MACRO HELP!!

[rebelhunter]

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??

 

[DonkeyOte]

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]

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