Changing CommandBars.Name

[Opv]

Is there anything that would inherently prevent me from changing the Name of my dynamically created toolbar more than once? I am wanting to append the current Name with an updated count whenever the number of terms in the target range changes so that I have an accurate running count visible at all times.


Private Sub Worksheet_Change(ByVal target As Range)

Dim test As Integer
test = Sheets("Dictionary").Range(startRange, Range(startRange).End(xlDown)).Count

Application.CommandBars(myToolBar).Name = myToolBar
Application.CommandBars(myToolBar).Name = myToolBar & ", " & test & "terms in Dictionary!"
End Sub

This tends to work when the first time a change is made; however, on all subsequent changes I receive an "invalid procedure call or Argument" error. Is there a way to change this to make the Name update after all detected changes?

Opv

[mikerickson]

My testing suggests that you can change the name as often as you like. Excel 2004 has no objections to either the comma or the exclamation mark.

I did run into your error in earlier testing when I tried to refer to the CommandBar by its old name ("Custom 2") after it had been changed once.

What is the variable myToolBar? a name or an index?

On further reflection...How would nameing a CommandBar make the number of terms "visible at all times"?
The .Caption of a CommandBarControl could be put to this use, but I don't know how to see the .Name of a CommandBar without having to invoke a dialog box.

[Opv]

My testing suggests that you can change the name as often as you like. Excel 2004 has no objections to either the comma or the exclamation mark.

I did run into your error in earlier testing when I tried to refer to the CommandBar by its old name ("Custom 2") after it had been changed once.

What is the variable myToolBar? a name or an index?

myToolBar is a string containing the name used when the toolbar is created.

After further testing, I've learned that a new toolbar is being created with each name change. It never dawned on me that I should capture the current name, delete the existing toolbar and then recreate it with the new name. Even doing that, I'm still running into the one-time only problem.

Perhaps the Toolbar name is not the most appropriate place to display the running count. I guess I could do it in the Windows title bar.

Thanks,

Opv

[Opv]

Well, I achieved the desired results by changing the Windows title bar rather than the toolbar caption. I used the following code:


Sub trackTerms()

Dim Terms As Integer
Dim myTitleBar As String

Terms = Range(startRange, Range(startRange).End(xlDown)).Count
myTitleBar = "English-Mvskoke Dictionary - " & Terms & " terms currently in Dictionary!"

If ActiveWorkbook.Name = "myFileName.xls" Then

Windows(1).Caption = ""
Application.Caption = myTitleBar

Else: Windows(1).Caption = ActiveWorkbook.Name
Application.Caption = "Microsoft Excel"

End If

End Sub

This works fine on the workbook in question, but when I have more than one workbook open and switch windows, the title bar is comprised of the new change PLUS the current active workbook's e name.

Is there a way to correct this code so that it changes only when the myFileName.xls workbook is active and reverts to the default setting when another workbook is active?

Opv

[mikerickson]

You could put routines similar to that one in the Workbook_Activate and Workbook_Deactivate events. One putting your custom Caption, the other restoring the default Caption.

[Opv]

You could put routines similar to that one in the Workbook_Activate and Workbook_Deactivate events. One putting your custom Caption, the other restoring the default Caption.

That did the trick. Thanks.

One additional quirk I noticed is that when I go to switch windows from the "Windows" menu, the file name in the drop down menu is displayed as "2" (or another number, depending on the number of workbooks that are open at the time). I don't suppose there is a way to correct this using VBA?