VBA for opening file/populating combo box with sheet names

[Sinobato]

Hello again Excel VBA gurus!

I am almost halfway now on my Excel application I'm doing and woul
just like to ask for some help on what I'm supposed to happen once
run my scripts:

1. When the user click a command button, I would like to inquire wha
file he would like to open and put that filename on a variable, lik
when you do a File -> Open.
2. Once the file is opened, I would like to populate a combo box or
drop-down list with the names of the sheets on the workbook and plac
on another variable the sheet that the user had chosen so that I ca
use it to determine which sheet will I want my main script to work on.

Last question, on Excel, what is the difference when you use objects o
the Forms toolbox as compared with using the control toolbox?

Thanks as always for your help!
Sinobat

 

[Bob Phillips]

1. Take a look at the GetOpenFileName method, as it allows the user to
select the file. It doesn't open it, just returns the full path which you
can save in a variable.

2. Save the sheet names in a hidden worksheet, and link your combobox to
that range.

Forms controls are maybe easier to use, but control toolbox controls are
more flexible.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Sinobato]

Bob,

Thanks for the information! However, can you give me a sample code o
how I can get the sheet names from workbook? I don't have an idea ho
can I iterate through the sheets on the workbook and get the names.

Thanks again!
Sinobat

 

[Bob Phillips]

Hi Sinobato,

Okay, once you have opened the workbook, this code will get the sheet names
and store them in a sheet called Hidden in column A

i=0
For Each sh In Activworkbook.Worksheets
i=i+1
Worksheets("Hidden").Range("A" & i).Value = sh.Name
Next i

To link the combox to the range use


With ActiveSheet.ComboBox1
.ListFillRange = "Hidden!" & "A1:A" & i
.ListIndex = 0
End With

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)