Macro to select file

Posted by Mike on August 07, 2001 4:50 PM

Righto, this is a biggie (I think)

I'm trying to design an interface to a wee database exercise I'm doing...and I need a way of sorting through to the required sheet.

Individuals have thir information on a single sheet in a workbook containing the country's individuals (named {country name}.xls). These country sheets are in a folder named after the region (Europe etc).

I need to be able to prompt the user to select the region from a list (directory list?), select a corresponding country (have that wookbook open, and read the sheet names) and then prompt for an individual which will then become the active sheet.

Am I dreaming or is this possible?

Any help/suggestions would be appreciated.

Thanks

-Mike

Posted by Malc on August 08, 2001 1:16 AM

Use a user form with three combo boxes.
The first combo box has the row source linked to a named range with the regions.

Then using the combo box change option write code to change the row source of the next combo box the the appropriate list of countries.
e.g Case select Regions
Case "Europe"
combobox2.rowsource="Europe"

Case "USA"
Combobox2.rowsource="USA"

etc.

Then open the country workbook get the list of sheets in that workbook then use the additem function to load the third combo box with the sheet names (Cities?).

Workbook("SelectedCountry").activate
for x = 10 worksheets.count
Userform1.combobox2.additem worksheets(x).name ' check the syntax on the .name if it doesn't work it won't be far off
next x

Then finally select the city sheet
e.g. workbook("SelectedCity.xls").activate
Sheets(combox3.listindex).select
end
end sub
Without writing the whole thing and trying it I'd never get the syntax correct but it should be a start.