PDA

View Full Version : Sheets Selection Through Combobox



ayazgreat
02-10-2008, 12:18 AM
Sir

I have got 10 sheets in a workbook having different names. I want a ComboBox in every sheet displaying names of all sheets in combobox and when i select a name of sheet from combobox it goes to that sheet after hidding sheet where selection was made and running macro codes which i have developed for sheet. I want only one sheet display on the screen when one selection is made other one is invisible.

Ayazgreat

Bob Phillips
02-10-2008, 02:22 AM
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("SheetList").Delete
On Error GoTo 0
End Sub

Private Sub Workbook_Open()
Dim sh As Worksheet


On Error Resume Next
Application.CommandBars("SheetList").Delete
On Error GoTo 0

With Application.CommandBars.Add(Name:="SheetList", temporary:=True)

With .Controls.Add(Type:=msoControlComboBox)

For Each sh In ThisWorkbook.Worksheets

.AddItem sh.Name
Next sh
.OnAction = "SelectSheet"
End With
.Visible = True
End With

End Sub


This is workbook event code.
To input this code, right click on the Excel icon on the worksheet
(or next to the File menu if you maximise your workbooks),
select View Code from the menu, and paste the code

And then in a standard code module, add



Public Sub SelectSheet()
Dim sh As Worksheet

With Application.CommandBars.ActionControl

ThisWorkbook.Worksheets(.Text).Visible = xlSheetVisible
For Each sh In ThisWorkbook.Worksheets

If sh.Name <> .Text Then

sh.Visible = xlSheetHidden
End If
Next sh
End With
End Sub

ayazgreat
02-10-2008, 02:42 AM
Thanks Sir for your reply but i am sorry to say that I have inserted combobox in sheet from form in sheet so in this case i need a macro for inserted combobox from selection sheets there as mentioned below.



file:///C:/DOCUME~1/AYAZAK~1/LOCALS~1/Temp/msohtml1/01/clip_image001.gif

ayazgreat
02-10-2008, 02:54 AM
Kindly see attached file for example.

ayazgreat
02-10-2008, 03:10 AM
Tow more problems iare that if new sheets are inserted then

1- More sheets are visible then one
2- Sheets box is not updated when new sheets are inserted.

Bob Phillips
02-10-2008, 07:30 AM
I am not going to give you a solution along your suggestion, that is just a dumb way of doing it.

Here is how you should do it. If you like it, use it, if not, throw it away.

ayazgreat
02-10-2008, 11:15 AM
Thanks i will try to use it

vipin242004
11-27-2008, 02:26 AM
Thanx dude...will try that.