Customizing commandbar problems

[David Cuthill]

I am trying to customize the shapes commandbar on a
worksheet activation and continually get an error when I
attempt to delete the format object... item from the menu.

"Invalid procedure call or argument"

This is a one time error message and then it seems to
disappear any other times I access the worksheet while the
workbook is open.

Does anyone have any insight into this as it is becoming
very frustrating.

David

Private Sub Worksheet_Activate()
Application.CommandBars("Shapes").Reset
Application.CommandBars("Shapes").Enabled = True
Application.OnKey "^x", "CompDelete"
Application.OnKey "{delete}", "CompDelete"
Application.OnKey "{insert}", "CompInsert"
With Application.CommandBars("Shapes")
.Controls.Add(Type:=msoControlButton,
Before:=1).Caption = "Insert Picture"
.Controls("Insert Picture").OnAction = "CompInsert"
.Controls("Insert Picture").FaceId = 295
.Controls.Add(Type:=msoControlButton,
Before:=1).Caption = "Delete Picture"
.Controls("Delete Picture").OnAction = "CompDelete"
.Controls("Delete Picture").FaceId = 292
.Controls("Format Object...").Delete
.Controls("Cut").Delete
.Controls("Paste").Delete
.Controls("Copy").Delete
.Controls("Grouping").Delete
.Controls("Order").Delete
.Controls("Assign Macro...").Delete
.Controls("Set Autoshape Defaults").Delete
.Controls("Hyperlink...").Delete
End With

End Sub

 

[Jim Cone]

David,

"Format Object" does not appear on the Shapes Command Bar.
It can appear on the "Button", "Object /Plot" command bars depending on your Excel version.

Regards,
Jim Cone
San Francisco, CA

 

[David Cuthill]

That's weird since it shows up on the same bar as all the
other things I'm trying to delete. Is it possible to have
multiple bars open since the shape is a
groupobject/drawingobject?

I'll try also closing the object command bar.

David

-----Original Message-----
David,

"Format Object" does not appear on the Shapes Command Bar.
It can appear on the "Button", "Object /Plot" command

bars depending on your Excel version.

Regards,
Jim Cone
San Francisco, CA

"David Cuthill" <[email protected]>

wrote in message [email protected]...

 

[Jim Cone]

David,

The Shapes command bar is a "shortcut" bar that pops up when you right-click a shape?

The XL97 "Shapes" (70) command bar contains:
Cu&t &Copy &Paste Edit Te&xt &Grouping O&rder Assig&n Macro...
Set AutoShape &Defaults &Object... &Hyperlink
I show 44 different shortcut bars in XL 97

The XL2002 "Shapes" (92) command bar contains:
Cu&t &Copy &Paste Recon&vert Edit Te&xt &Grouping O&rder Assig&n Macro...
Set AutoShape &Defaults &Object... &Hyperlink... Edit &Hyperlink...
&Open Hyperlink &Remove Hyperlink
I show 60 different shortcut bars in XL 2002.

Regards,
Jim Cone
San Francisco, CA

That's weird since it shows up on the same bar as all the
other things I'm trying to delete. Is it possible to have
multiple bars open since the shape is a
groupobject/drawingobject?
I'll try also closing the object command bar.
David
bars depending on your Excel version.

- snip -

 

[Greg Wilson]

David,

In addition to Jim's comment, I suggest you try running
the below CheckSpelling macro. You'll be in for a surprise.

I rewrote your macro using a loop to delete the controls
instead. Make sure that the macros CompInsert and
CompDelete are in a standard code module instead of the
worksheet code module.

Sub CheckSpelling()
Dim CB As CommandBar
Dim Ctrl As CommandBarControl
Set CB = Application.CommandBars("Shapes")
For Each Ctrl In CB.Controls
MsgBox Ctrl.Caption
Next
End Sub

Private Sub Worksheet_Activate()
Dim CB As CommandBar
Dim Ctrl As CommandBarControl
With Application
.OnKey "^x", "CompDelete"
.OnKey "{DELETE}", "CompDelete"
.OnKey "{INSERT}", "CompInsert"
Set CB = .CommandBars("Shapes")
End With

For Each Ctrl In CB.Controls
Ctrl.Delete
Next
Set Ctrl = CB.Controls.Add(Type:=msoControlButton)
With Ctrl
.Caption = "Insert Picture"
.OnAction = "CompInsert"
.FaceId = 295
End With
Set Ctrl = CB.Controls.Add(Type:=msoControlButton)
With Ctrl
.Caption = "Delete Picture"
.OnAction = "CompDelete"
.FaceId = 292
End With
End Sub

Regards,
Greg

 

[David Cuthill]

Thanks Jim and Greg everything now works as hoped. The
checkspelling macro is very useful.


David