controlling worksheets using combo-box

[Sunil Sagar]

Hi All,

I want to control worksheets using drop-down boxes (combo box).
what I want to do is, I have many worksheets are written in one excel file.
Now I want to use excel sheet as, on first page I am creating one drop down.

This will have lists as a, b, c, ..

and when we will select a from dropbox.. it will make visible the worksheet
with name "a" and other worksheets will be hidden.

Please let me know, if it is possible to accomplish the above task ..

Sunil Sagar

 

[OssieMac]

Assuming that you want to use ActiveX type combo box (Selected from the
ActiveX options on the Insert Button on the Developer ribbon in xl2007 or
from the Control Toolbox toolbar in pre xl2007 versions ).

Further I assume that you do not want to hide the mains sheet with the
dropdown combo box.

Create a list of your worksheets somewhere out of the way, preferrably on
the worksheet where you are placing the combo box.
Create the combo box and while in Design Mode, set the List Fill Range to
the range of your worksheet list. (Something like Z1:Z30). Right click the
combo box and select View Code. Insert the following code between the Private
Sub .... and End Sub that appears in the VBA editor. Close the VBA editor and
save the workbook. Don't forget to turn off Design Mode.

Dim ws As Worksheet
Dim strSelectedWS As String

strSelectedWS = ComboBox1.Value

For Each ws In Worksheets
'Edit Sheet1 to the name of your worksheet with the combo box.
If ws.Name = "Sheet1" Or ws.Name = strSelectedWS Then
ws.Visible = xlSheetVisible
Else
ws.Visible = xlSheetHidden
End If
Next ws