Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
controlling worksheets using combo-box
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
controlling worksheets using combo-box
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 -- Regards, OssieMac "Sunil Sagar" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
combo boxes & protecting worksheets | Excel Worksheet Functions | |||
Is there a key combo to toggle between worksheets? | Excel Discussion (Misc queries) | |||
All Worksheets in a Data Validation combo | Excel Worksheet Functions | |||
Controlling Userforms | New Users to Excel | |||
Controlling Sequence | Excel Discussion (Misc queries) |