The following code get sheet names that are prefixed with "saf". But I had to put in code that would delete only sheet names and not any other hard coded contols.
The first part deletes sheet names and then re-adds them in the second part.
Problem--the sheets are put in the control in reverse order, BUT when you take out the first delete routine the sheet names are in the correct order???<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, 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 23:42
The first part deletes sheet names and then re-adds them in the second part.
Problem--the sheets are put in the control in reverse order, BUT when you take out the first delete routine the sheet names are in the correct order???<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, 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 23:42