COMMAND BAR - need code to put sheet names in customs CBs as

[itr674]

I am using the following code to put worksheet names in controls that are made from a MenuMaker sheet. I had to start using 2 commandbars because I could not find a way to wrap the CBs.

Problem - how to get sheet names to go to the applicable custom CB controls. Line 13 is where I am running into problems ...<pre>Option Explicit

Dim CB As CommandBar
Dim WS As Worksheet
Dim MenuSheet As Worksheet
Dim MenuObject As CommandBarPopup
Dim MenuItem As Object 'used in case 2 and 3 for items under menu
Dim SubMenuItem As CommandBarButton
Dim WSNum As Integer
Dim strName As String
Dim strActionName As String

Sub WS_Get_Listings(cntrl As String, prefix As String)

On Error Resume Next
Set MenuObject = Application.CommandBars("??????").Controls(cntrl)

'Delete existing Worksheet names but not hard coded buttons
For Each MenuItem In MenuObject.CommandBar.Controls
For Each WS In Worksheets
If Left(WS.Name, Len(prefix)) Like prefix Then
' Remove the sheet prefix
strName = Right(WS.Name, Len(WS.Name) - Len(prefix))
If strName = MenuItem.Caption Then MenuItem.Delete
End If
Next
Next

strActionName = ThisWorkbook.Name & "!GoTo_ListingsSheets"
'adds all worksheets meeting criteria
WSNum = 0
For Each WS In Worksheets
If Left(WS.Name, Len(prefix)) Like prefix Then
'Remove the prefix
strName = Right(WS.Name, Len(WS.Name) - Len(prefix))
WSNum = WSNum + 1 ' this ensures WS are at top of control in the order _
they appear in workbook AND puts hard coded controls at _
bottom of control. Divider is used to seperate the two ...
MenuObject.Controls.Add(Before:=WSNum).Caption = strName
MenuObject.Controls(strName).OnAction = strActionName
MenuObject.Controls(strName).DescriptionText = WS.Name
End If
Next
On Error GoTo 0

End Sub

Sub WS_Get_FIL()
WS_Get_Listings cntrl:="File Maint", prefix:="fil-"
End Sub
Sub WS_Get_SAF()
WS_Get_Listings cntrl:="Safety", prefix:="saf-#"
End Sub</pre>

TestmenuMaker.xls
	A	B	C	D	E	F	G	H	I	J
2	Command	BarSetup			CBsShown
3	MenuLevel	NameCaption	Position	Macro	Divider	FaceID	Futureuse	Futureuse	Futureuse
4	CommandBar	Test	1
5	Menu	FileMaint		WS_Get_FIL
6	MenuItem	ShowToolbars		CB_ShowAll	TRUE
7	ComBarButton	IntroSheet		GoToIntro	TRUE
8	Menu	Safety		WS_Get_SAF	TRUE
9	Menu	InspectionInfo		WS_Get_INP	TRUE
10	MenuItem	test45			TRUE
11	Menu	Misc		WS_Get_MIS	TRUE
12	CommandBar	Test2	2
13	Menu	Safety		WS_Get_SAF	TRUE
14	Menu	InspectionInfo		WS_Get_INP	TRUE
15
fil-Menu Maker-2

This message was edited by em on 2002-11-06 08:58
This message was edited by em on 2003-02-09 17:25

 

[itr674]

Throwing this back out there for hope ...

 

[itr674]

Throwing back out there one last time ...