Sheets Selection Through Combobox

[ayazgreat]

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]

[vba]

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
[/vba]

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

[vba]

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
[/vba]

[ayazgreat]

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.



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

[ayazgreat]

Kindly see attached file for example.

[ayazgreat]

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]

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]

Thanks i will try to use it

[vipin242004]

Thanx dude...will try that.