passing commandbars to a procedure

[Doug Glancy]

I have a procedure that takes a commandbar object as it's argurment:

Sub modify_insert_delete_buttons(cbar As CommandBar, Optional delete As
Boolean = False)

It modifies Excel's existing Insert Rows and Delete Rows buttons on various
Excel commandbars including the Edit menu and the Cells right-click menu.
In the procedure I need to use FindControl which seems to be only available
with CommandbarPopups, not Commandbars. In order to pass the Edit menu to
my routine I've done this in the calling procedure:

Dim cbar As CommandBar
Set cbar = Application.CommandBars("Worksheet Menu
Bar").FindControl(ID:=30003).Controls(1).Parent 'Edit menu

Is there a better way to do this, without calling the Parent of a Control on
the Edit menu? It seems like the following should work, but it gives a type
mismatch error:

Set cbar = Application.CommandBars("Worksheet Menu
Bar").FindControl(ID:=30003)

Again, the way I'm doing it works, I just wonder if there's a better way.

thanks,

Doug

 

[Guest]

Doug,

I suspect you overlooked cbar's type declaration. It should be declared as a
CommandBarControl instead of a CommandBar. The following returns "&Edit" for
me:

With Application
Set cbar = .CommandBars(1).FindControl(ID:=30003)
MsgBox cbar.Caption
End With

Interestingly, (and this is just my take on it) the parent of a menu item is
NOT the CommandBarPopup (i.e. "Edit" in this case) but the dropdown that the
menu items are attached to. This is a modified popup Commandbar. That is why
the following code didn't return an error with cbar declared as CommandBar:

Set cbar = Application.CommandBars("Worksheet Menu
Bar").FindControl(ID:=30003).Controls(1).Parent

Note that the following returns "&Edit" which (IMO) proves my point. And
note the double "Parent":

With Application
Set cbar = .CommandBars(1).FindControl(ID:=30003).Controls(1).Parent.Parent
MsgBox cbar.Caption
End With

However, it's not that simple. The first level child to the "Edit" menu
should (according to my theory) be the dropdown. But, since it is not a
Control the following returns the caption of the first control on the
dropdown intstead (i.e. "Can't &Undo"):

With Application
Set cbar = .CommandBars(1).FindControl(ID:=30003)
MsgBox cbar.Controls(1).Caption
End With


Regards,
Greg

 

[Bob Phillips]

Doug,

Your preferred way will work, but cBar is a CommandbarControl in this case,
not type Commandbar.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Doug Glancy]

Greg,

Thanks, but I didn't really overlook the type declaration. Declaring it as
a CommandBarControl wouldn't work because I need to pass a Commandbar to the
subroutine, so that the subroutine can use FindControl on the Commandbar. I
was just wondering if there was a better way to be able to set Edit and
other menus as commandbars.

Your analysis about the "intermediate parent" is interesting. I fooled
around with it to see if I could use it for this, but didn't come with
anything.
Thanks,

Doug

 

[Bob Phillips]

If you want the parent of a control then you have to use .Parent as far as I
can see Doug.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)