Commandbars - want to force WS order by using numbers ...

[itr674]

I am using the following code to get worksheet names for commandbar, but need the sheet names to be in a particular order in the control.

To do this I put a number in with the sheet prefix before runing the sort routine, but need the number removed when the sheet name goes to the control. Sample sheet name "fil-1 Test Sheet", I can remove "fil-" but can not figure out how to get rid of the "1".

I have tried to use "Like" but can not get it to work?

<pre>Sub WS_Get_Listings(cntrl As String, prefix As String)

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

'Delete existing Worksheet names but not hard coded buttons
For Each MenuItem In MenuObject.CommandBar.Controls
For Each WS In Worksheets
If InStr(WS.Name, prefix) Then
' Remove the sheet prefix
strName = WorksheetFunction.Substitute(WS.Name, 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 InStr(WS.Name, prefix) Then
'Remove the prefix
strName = WorksheetFunction.Substitute(WS.Name, 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</pre>

 

[rikrak]

hi em,

Is this the trick?
<pre>
Sub WS_Get_FIL()
WS_Get_Listings cntrl:="File Maint", prefix:="fil-#"
End Sub

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
</pre>

 

[itr674]

rikrak - you did it again, looks like that worked.

have you had a look at the question about commnadbar height or wrapping a custom commandbar?

 

[rikrak]

Yes, had a look at it but have no answer on that (yet).