VBA for opening file/populating combo box with sheet names
 

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

--
Message posted from http://www.ExcelForum.com

VBA for opening file/populating combo box with sheet names
 

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 " wrote in message
...
Hello again Excel VBA gurus!

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

1. When the user click a command button, I would like to inquire what
file he would like to open and put that filename on a variable, like
when you do a File - Open.
2. Once the file is opened, I would like to populate a combo box or a
drop-down list with the names of the sheets on the workbook and place
on another variable the sheet that the user had chosen so that I can
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 on
the Forms toolbox as compared with using the control toolbox?

Thanks as always for your help!
Sinobato


---
Message posted from http://www.ExcelForum.com/

VBA for opening file/populating combo box with sheet names
 

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

--
Message posted from http://www.ExcelForum.com

VBA for opening file/populating combo box with sheet names
 

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)

"Sinobato " wrote in message
...
Bob,

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

Thanks again!
Sinobato


---
Message posted from http://www.ExcelForum.com/