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

itr674

Well-known Member
Joined
Apr 10, 2002
Messages
1,778
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>
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

rikrak

Active Member
Joined
Aug 21, 2002
Messages
255
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

Well-known Member
Joined
Apr 10, 2002
Messages
1,778
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?
 

Forum statistics

Threads
1,144,509
Messages
5,724,770
Members
422,577
Latest member
kayth891

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top