drop down list on form
 

Hi there

I have a form that opens automatically when the file is opened. I want to
add a drop down list to the form that has the names of the worksheets, and
when one is selected, it goes to the worksheet selected.

Is that possible, if so, how is it done?


--
Thank-you!
Ruth

drop down list on form
 

There will be a easier way than this however this is one way

It can be doe not in one action but two

Set up you list ie Sheet 1, sheet 2, sheet3 etc

Us the dropdown list cell number in an If Formula which will be in the cell
above your drop down list

=IF(M16="SHEET 1",HYPERLINK("#'Road Cash Accounting'!AI2","Road cash
accounting PLEASE PRESS"), IF(M16="SHEET 2" etc,tec

In this case if sheet one is selected in your drop down list in M16,the
cell containing the above formula will appear as A HYPERLINK TO "road cash
accounting PLEASE PRESS"
THIS WOULD TAKE YOU TO ROAD CASH ACCOUNTING SHEET CELL AI2

IN excel 2003 you can have 7 if formulas , in 2007 I believe more

Hope this helps

Stewart





"Ruth" wrote:

Hi there

I have a form that opens automatically when the file is opened. I want to
add a drop down list to the form that has the names of the worksheets, and
when one is selected, it goes to the worksheet selected.

Is that possible, if so, how is it done?


--
Thank-you!
Ruth

drop down list on form
 

I think this is what you want, code is on the userform
Private Sub ComboBox1_Change()
Sheets(ComboBox1.Text).Select
End Sub

Private Sub UserForm_Activate()
For Each Sheet In ActiveWorkbook.Sheets
ComboBox1.AddItem (Sheet.Name)
Next
End Sub
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Ruth" wrote:

Hi there

I have a form that opens automatically when the file is opened. I want to
add a drop down list to the form that has the names of the worksheets, and
when one is selected, it goes to the worksheet selected.

Is that possible, if so, how is it done?


--
Thank-you!
Ruth

drop down list on form
 

Sorry to take so long to get back-- had to set the project aside for a bit

Thank-you both. I used John's answer- it just seemed a bit easier and it
worked great.

This there a code that will open the worksheet so that cell A1 is the active
cell when it opens?


--
Thank-you!
Ruth


"John Bundy" wrote:

I think this is what you want, code is on the userform
Private Sub ComboBox1_Change()
Sheets(ComboBox1.Text).Select
End Sub

Private Sub UserForm_Activate()
For Each Sheet In ActiveWorkbook.Sheets
ComboBox1.AddItem (Sheet.Name)
Next
End Sub
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Ruth" wrote:

Hi there

I have a form that opens automatically when the file is opened. I want to
add a drop down list to the form that has the names of the worksheets, and
when one is selected, it goes to the worksheet selected.

Is that possible, if so, how is it done?


--
Thank-you!
Ruth