Worksheet Names - code putting them in reverse order?

[itr674]

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

 

[Damon Ostrander]

Hi em,

Yes, it appears that your code should add the sheet buttons right-to-left. However, it is never a good idea to count on the For..Each loop referencing objects in any particular order. If you want control of the order you should always use a For loop that has an index. For example, you could force your second loop to go from last to first worksheet with the code:


Dim iWS As Integer
For iWS = Worksheets.Count To 1 Step -1
Set WS = Worksheets(iWS)
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 iWS

 

[itr674]

Damon - thanks, that's the first time I've seen the "-1" used. How did you learn VBA?

 

[Damon Ostrander]

Hi em,

VBA is not my first programming language. I've been programming in other languages for over 25 years, so there were certain features (such as the Step in the For loop) that I expected to see. The surprise for me was when I realized that VBA was a real programming language--I had assumed up to that point that it was a "baby" language or a simple scripting language. There are still some features I would like to see added, but VBA is way more powerful than what I would have expected.

I am probably one of the few people who have learned almost everything I know about VBA directly from the built-in help files. Maybe I'm just to cheap to buy any of the "step-by-step" or "power programming books." My learning was also accelerated greatly by teaching several courses on VBA. As they say, the teacher always learns more than the students.

Good luck with your own learning experience.

Damon