Any body know how to check if a com 2

[VBAva]

Any body know how to check if a command bar exists or not?

i have a custom bar in my program that gets changed at different stages, but i the user deletes it then an error occurs when i try to change it because it does not exist.

So i have tried the CommandBars.FindControl method but am not sure about the verbage.

this method returns a CommandBarControl but i just need true or false.
something like this maybe

(pseudo code)
If CommandBars("Custom Edit") Exists = True Then
    UpdateBar
Else
   MsgBox "Toolbar does not exist"
End If

thank you

 

[TonyJollans]

Hi VBAva,

Try this ..

On Error Resume Next
If CommandBars("Custom Edit") Is Nothing Then
    MsgBox "Toolbar does not exist"
Else
    UpdateBar
End If

Enjoy,
Tony

 

[TonyJollans]

Hi VBAva,

Actually that isn't really very good code; this is better ..

Dim cbar as CommandBar
On Error Resume Next
Set cbar = CommandBars("Custom Edit")
On Error Goto 0
If cbar Is Nothing Then
    MsgBox "Toolbar does not exist"
Else
    UpdateBar
End If

Enjoy,
Tony

 

[VBAjedi]

Or, to avoid assuming you know what the error was (even if it is pretty safe to do so):

For Each cBar In CommandBars
   If cBar.Name = "Display Avail Tools" Then
      UpdateBar
   End If
Next cBar

That should not cause an error at all.


VBAjedi

 

[VBAva]

Thats great, i knew it was nothing too complicated but i just could not find the right commands

 

[VBAva]

Thanks for the help
I was writing a reply with a a question, then i figured out the problem. Just incase anyone else has the same problem

First reply.....

i dont know what i am doing wrong though because i keep getting 'Run-Time Error 424' Object Required on the line
For Each cBar In CommandBars
i tried not using dim statement for cBar, with and without Option Explicit, so i am thinking that the error is the CommandBars statement

Private Sub Workbook_Activate()
Dim cBar As CommandBar

For Each cBar In CommandBars
   If cBar.Name = "Custom Edit" Then
      Call basBars.UpdateCustomTool
   End If
Next cBar

end sub

Solution...

The CommandBars object is not valid in the MicrosoftExcelObjects part of a project, it is only valid (and therefore only works) in the modules. this works now

Private Sub Workbook_Activate()
    Call basBars.FindBar
End Sub

(basBars)
Public Sub FindBar()
Dim cBar As CommandBar

For Each cBar In CommandBars
   If cBar.Name = "Custom Edit" Then
      Call basBars.UpdateCustomTool
      End
   End If
Next cBar

End Sub

 

[krinid]

Is that true? It only works in modules? Why is that? Are there other objects that only work in modules?

 

[TonyJollans]

Hi VBAva,

The Commandbars Collection belongs to the Application Object. In a module that is the default. In Excel Objects you need to explicitly state it, so ..

Dim cBar As CommandBar

For Each cBar In

Application.

CommandBars
   If cBar.Name = "Custom Edit" Then
      Call basBars.UpdateCustomTool
   End If
Next cBar

.. should work anywhere.

Enjoy,
Tony

 

[VBAva]

Ah, i just knew it did not work, thanks for that

I think that also answers your question krinid

im learning a lot

 

[krinid]

Indeed it did answer my question! cheers