ComboBox for selection sheet and run macro

[ayazgreat]

Sir

I have got 8 sheets in a workbook having the following names first one is Main,and others are Aw,Nzm,Mhk,Gul,Qta,and Hdr.

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.

Thanks in advance.

Ayazgreat

 

[ayazgreat]

One things i want to ask more if same sheet is selected from the combobox does it show error or do nothing.

 

[ayazgreat]

Is there anbody to solve my question?

Ayazgreat

 

[Tom Urtis]

Instead of placing a button on every sheet, you may be interested in considering a simple combobox on the menu bar, which would always be visible no matter what sheet is active (because it's the menu bar) and requires no hyperlinks or drop-downs in worksheet cells.

Try this as an alternative:

Step 1
Find the little Excel workbook icon near the upper left corner of your workbook window, usually just to the left of the File menu option. Right click on that icon, left click on View Code, and paste the following procedures into the large white area that is the workbook module:

Private Sub Workbook_Open() 
Run "ResetMenu" 
Run "MakeCBO" 
Dim TymeOfDay$ 
If Time < 0.5 Then 
TymeOfDay = "Good Morning !!" & vbCrLf & vbCrLf 
ElseIf Time >= 0.5 And Time < 0.75 Then 
TymeOfDay = "Good Afternoon !!" & vbCrLf & vbCrLf 
Else 
TymeOfDay = "Good Evening !!" & vbCrLf & vbCrLf 
End If 
MsgBox _ 
TymeOfDay & _ 
"To quickly and easily activate any sheet," & vbCrLf & _ 
"select a sheet name from the drop-down list" & vbCrLf & _ 
"that is located on the menu bar near the top" & vbCrLf & _ 
"of the screen, next to the ''Help'' button.", _ 
64, "Sheet navigation tip:" 
End Sub
 
Private Sub Workbook_Activate() 
Run "ResetMenu" 
Run "MakeCBO" 
End Sub
 
Private Sub Workbook_BeforeClose(Cancel As Boolean) 
Run "ResetMenu" 
ThisWorkbook.Save 
End Sub
 
Private Sub Workbook_Deactivate() 
Run "ResetMenu" 
End Sub

Step 2
While you are still in the Visual Basic Editor, from that menu bar, click Insert > Module and paste the following code into that new module:

Private Sub ResetMenu()
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("Sheet selector").Delete
Err.Clear
End Sub
 
Private Sub MakeCBO()
With Application
.ScreenUpdating = False
Run "ResetMenu"
Dim cboSheetz As CommandBarComboBox, ws As Worksheet
With .CommandBars("Worksheet Menu Bar")
Set cboSheetz = .Controls.Add(Type:=msoControlComboBox, before:=.Controls.Count)
End With
With cboSheetz
.Caption = "Sheet selector"
.OnAction = "mySheet"
End With
For Each ws In Worksheets
cboSheetz.AddItem ws.Name
Next ws
.ScreenUpdating = True
End With
End Sub
 
Private Sub mySheet()
Application.ScreenUpdating = False
With CommandBars("Worksheet Menu Bar").Controls("Sheet selector")
With Worksheets(.List(.ListIndex))
.Visible = xlSheetVisible
.Activate
End With
Dim ws As Worksheet
For Each ws In Worksheets
If ws.Name <> .List(.ListIndex) Then ws.Visible = xlSheetVeryHidden
Next ws
End With
Application.ScreenUpdating = True
End Sub

Step 3
Press Alt+Q to return to the worksheet.

Step 4
Save the workbook.

Step 5
Close the workbook.

Step 6
Re-open the workbook and see if what happens is what you want, which will be:
- a message box advises the user of the combobox so they know what is going on.
- a combobox with visible sheets' names is on the menu bar.
- the combobox will only be visible when that workbook is active.
- selecting a sheet name will take you to that sheet and hide all the other sheets, which is what you said you wanted.

 

[ayazgreat]

Thanks for your reply

but If i want a combo box on the sheet because i have a main menu sheet where it will be displying and with the selection of sheet name from combobox i have to run my report codes to display result. If possible



Thanks in advance
Ayazgreat

 

[Tom Urtis]

You can tie in your sheet macro to the sheet's Activate event instead of redundantly using a combobox on every sheet.

If you really want a combobox on each scheet, then just go ahead and stick one on each sheet from the control toolbox and populate it with the sheet names, either via the rowsource property if you list each sheet name on a separate sheet in (presumably) a hidden column, or you can amend the code I posted to populate each combobox when the workbook opens or is activated. Then run the "mySheet" macro when a combobox item is selected or whatever other event you had in mind, depending on what it is you are actually doing.

 

[ayazgreat]

Something more I want to know that if any condition i have to see all sheets are visible what should I do for it ?

 

[Tom Urtis]

I already answered that with this code block in the mySheets macro I posted, so it is taken care of:

Dim ws As Worksheet
For Each ws In Worksheets
If ws.Name <> .List(.ListIndex) Then ws.Visible = xlSheetVeryHidden
Next ws

 

[ayazgreat]

Thank you very much Sir but if i want to run reconcile report with the selection of sheet name from combobox/menu bar selection how should i adjust them with sheet selection.

Ayazgreat

 

[Tom Urtis]

I answered that for you already too. Assuming your combobox on any given sheet is named "ComboBox1" then amend the code I posted in the mySheets macro to simply this, which will activate the sheet whose name you selected from the drop-down list, and will hide all the other sheets.

Private Sub ComboBox1_Change()
Application.ScreenUpdating = False
With ComboBox1
With Worksheets(.List(.ListIndex))
.Visible = xlSheetVisible
.Activate
End With
Dim ws As Worksheet
For Each ws In Worksheets
If ws.Name <> .List(.ListIndex) Then ws.Visible = xlSheetVeryHidden
Next ws
End With
Application.ScreenUpdating = True
End Sub

One correction on a previous post I made, because we are dealing with embedded comboboxes, I referred to a rowsource property which, in the case of ole comboboxes, is listfillrange as I should have said.