How do I change a worksheet in Excel by using a combo-box?

[Guest]

I want to change the worksheet in Excel by choosing it in a combo-box that
displays 18 diferent options, and each one of these options displayed in the
combo-box, corresponds to a diferent worksheet, is it possible?

 

[Tom Ogilvy]

Possibly

Private Sub Combobox1_Click()
if Combobox1.ListIndex <> -1 then
Worksheets(Combobox1.Value).Select
end if
End Sub
This assumes the text in the combobox is the name of the sheet.

 

[Guest]

Tom I'm starting with programing and I think I understood the code u wrote to
me but how can I Know the name of the combo-box I've created, because in your
example u call it Combobox1, right, but in Excel the combo-box as a different
name, right?

 

[Tom Ogilvy]

In design mode, right click on the combobox and select view code. This
should take you to the click event of the combobox and you can see what the
name is.

 

[Guest]

Sorry Tom but this code is not suitable for what I'm trying to do because, I
need to change the worksheet by selecting it in a combo box, not that the
combo assume the name of a worksheet!

For example, in the array I have Sheet 2, Sheet 3 and the combo box is in
Excel's sheet 1. When I choose in the combo box Sheet 2 I should go to this
worksheet

Do u think u can help on this?

Thanks in advanced!

 

[Tom Ogilvy]

If the combobox in Sheet1 contains the entry Sheet2 in the dropdown list,
then if you assign the code I provided to the click event of the combobox in
Sheet1, it will do what you describe. I can't tell you what the name of the
combobox in sheet1 is nor did I ever recommend you name it the same as the
sheet name.
I told you how to discover the name of the combobox since you said you
didn't know it.

 

[Guest]

Tom
The problem is that when I assign the code to the click event the following
error occurs "Run time error: 424 - Object required" meaning that even after
I've changed the name of the combo box to Combobox1 and used the code u gave
something is still missing!

Is it possible if u can send me an Excel file with the code on it to my
email: (e-mail address removed), I think it it will be easier for me to
understand it and used.

Thanks again

 

[Tom Ogilvy]

OK, will do

--
Regards,
Tom Ogilvy

Tom
The problem is that when I assign the code to the click event the following
error occurs "Run time error: 424 - Object required" meaning that even after
I've changed the name of the combo box to Combobox1 and used the code u gave
something is still missing!

Is it possible if u can send me an Excel file with the code on it to my
email: (e-mail address removed), I think it it will be easier for me to
understand it and used.

Thanks again

 

[Guest]

Thanks a lot Tom this was realy helpful for me!

OK, will do