WILDCARDS - can you use them in code?

[itr674]

I use the following code to build commandbars from "prefixed" sheet names.

I need to add a "number" in the prefix, something like "saf1-Safety Schedules, saf2-Safety Problems" so I can get the sheets to show up in the control in predetermined order.

How do I filter out the numbers?<pre>Sub WS_Get_SAF()
On Error Resume Next
Set MenuObject = Application.CommandBars("CC Book").Controls("Safety")
' Delete existing "sheet" names but leaves hard coded controls in tack ...
For Each MenuItem In MenuObject.CommandBar.Controls
For Each WS In Worksheets
If Left(WS.Name, 3) = "saf" Then
' Remove the sheet suffix
strName = WorksheetFunction.Substitute(WS.Name, "saf-", "")
If strName = MenuItem.Caption Then MenuItem.Delete
End If
Next
Next
strActionName = ThisWorkbook.Name & "!GoToSAFSheets"
' Then add all sheets names meeting criteria
For Each WS In Worksheets
If Left(WS.Name, 3) = "saf" Then
' Remove the sheet name suffix
strName = WorksheetFunction.Substitute(WS.Name, "saf-", "")
MenuObject.Controls.Add(Before:=1).Caption = strName
MenuObject.Controls(strName).OnAction = strActionName
End If
Next
Run "KillVariables"
On Error GoTo 0
End Sub</pre>
This message was edited by em on 2002-10-17 09:18
This message was edited by em on 2002-10-17 23:44

 

[Andrew Poulsom]

You can use the Like operator:

If Left(WS.Name, 5) Like "saf-#" Then

where # is the wildcard for any single digit (0–9).

 

[itr674]

thanks Andrew ...

 

[itr674]

Andrew--have another question.

In the previous code the sheets are being listed in reverse order in the control (from the way they appear in the Workbook).

They use to list in order until I added the first deleting criteria section???

 

[itr674]

Andrew - can you see anything wrong with this code. I plugged in your suggested code but am getting an error.

One note, shouldn't I be able to change the code to saf#-test sheet and it still work?<pre>Sub WS_Get_SAF()
On Error Resume Next
Set MenuObject = Application.CommandBars("CC Book").Controls("Safety")
' Delete existing "sheet" names but leaves hard coded controls in tact ...
For Each MenuItem In MenuObject.CommandBar.Controls
For Each WS In Worksheets
If Left(WS.Name, 5) Like "saf-#" Then
'If Left(WS.Name, 3) = "saf" Then WAS USING THIS LINE AND EVERYTHING WORKED ???
' Remove the sheet suffix
strName = WorksheetFunction.Substitute(WS.Name, "saf-#", "")
If strName = MenuItem.Caption Then MenuItem.Delete
End If
Next
Next
strActionName = ThisWorkbook.Name & "!GoToSAFSheets"
' Then add all sheets names meeting criteria
For Each WS In Worksheets
If Left(WS.Name, 5) Like "saf-#" Then
' Remove the sheet name suffix
strName = WorksheetFunction.Substitute(WS.Name, "saf-#", "")
MenuObject.Controls.Add(Before:=1).Caption = strName
MenuObject.Controls(strName).OnAction = strActionName
End If
Next
Run "KillVariables"
On Error GoTo 0
End Sub</pre>

OnAction code:<pre>Sub GoToSAFSheets()
strCaller = "saf-#" & Application.CommandBars.ActionControl.Caption
Application.GoTo Sheets(strCaller).Range("A1"), True
End Sub</pre>
This message was edited by em on 2002-10-18 13:41

 

[rikrak]

hi em,

<quote>
Sub GoToSAFSheets()
strCaller = "saf-#" & Application.CommandBars.ActionControl.Caption
Application.GoTo Sheets(strCaller).Range("A1"), True
End Sub
</quote>

With what number has # to be substituted?
You have to store the original number somewhere to be able to substitute #.
I think this is not going to work this way.

 

[itr674]

rikrak - yea you are probably right. I thought about putting numbers on sheets so I could run a worksheet sort procedure in order to get the sheets to show up in the oreder I want in a command bar control.

The problem is that I have several menuitems that are hard coded to the control along with sheet names being added to the control. I want the hard coded controls to be at the bottom so we (for the MrExcel folks helping me) added "Before:=" top the code. The problem with that is that it puts the sheets names in reverse order when they go into the control. I was just trying to see if we could figure something out with code--I think I will have to just manually put the sheets in the oreder that I want them to make it work--although then the worksheet procedure might not be of much use ...
This message was edited by em on 2002-10-18 14:21

 

[rikrak]

In your other post you got a perfect solution from Andrew to your sortproblem.

To avoid the reverse order just count the number of hard codes items already in there and add the new items right above them:

num = number of items
for each item do:
item.add before:=num
loop

This way the new items will shift up above the hard codes items.
What about that?

 

[itr674]

rikrak -- your suggestion worked perfectly. I incorporated it as shown below. I have this procedure duplicated about ten times--the "WSNum" being in each one should not cause any problems should it?

<pre>Sub WS_Get_SAF()
WSNum = 0
On Error Resume Next
Set MenuObject = Application.CommandBars("CC Book").Controls("Safety")
MenuObject.TooltipText = "this button ..."
' Delete existing "sheet" names but leaves hard coded controls in tact ...
For Each MenuItem In MenuObject.CommandBar.Controls
For Each WS In Worksheets
If Left(WS.Name, 3) = "saf" Then
' Remove the sheet suffix
strName = WorksheetFunction.Substitute(WS.Name, "saf-", "")
If strName = MenuItem.Caption Then MenuItem.Delete
End If
Next
Next
strActionName = ThisWorkbook.Name & "!GoToSAFSheets"
' Then add all sheets names meeting criteria
For Each WS In Worksheets
If Left(WS.Name, 3) = "saf" Then
' Remove the sheet name suffix
WSNum = WSNum + 1
strName = WorksheetFunction.Substitute(WS.Name, "saf-", "")
MenuObject.Controls.Add(Before:=WSNum).Caption = strName
MenuObject.Controls(strName).OnAction = strActionName
End If

Next
Run "KillVariables"
On Error GoTo 0
End Sub</pre>

 

[rikrak]

Hi em,
it's not realy want i ment but if it works for you just leave it that way.

WSnum is no problem, even if it is declared global there's no problem, because it is set to 0 every time.