Customizing commandbar problems
 

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,
Befo=1).Caption = "Insert Picture"
.Controls("Insert Picture").OnAction = "CompInsert"
.Controls("Insert Picture").FaceId = 295
.Controls.Add(Type:=msoControlButton,
Befo=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

Customizing commandbar problems
 

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" wrote in message ...
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,
Befo=1).Caption = "Insert Picture"
.Controls("Insert Picture").OnAction = "CompInsert"
.Controls("Insert Picture").FaceId = 295
.Controls.Add(Type:=msoControlButton,
Befo=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

Customizing commandbar problems
 

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"
wrote in message news:473e01c47355$c4ccff80
...
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,
Befo=1).Caption = "Insert Picture"
.Controls("Insert Picture").OnAction = "CompInsert"
.Controls("Insert Picture").FaceId = 295
.Controls.Add(Type:=msoControlButton,
Befo=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
.

Customizing commandbar problems
 

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

"David Cuthill" wrote in message ...
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

- snip -

Customizing commandbar problems
 

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

-----Original Message-----
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"
wrote in message news:473e01c47355$c4ccff80
...
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,
Befo=1).Caption = "Insert Picture"
.Controls("Insert Picture").OnAction = "CompInsert"
.Controls("Insert Picture").FaceId = 295
.Controls.Add(Type:=msoControlButton,
Befo=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
.

.

Customizing commandbar problems
 

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


David



-----Original Message-----
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

-----Original Message-----
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"
wrote in message news:473e01c47355$c4ccff80
...
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,
Befo=1).Caption = "Insert Picture"
.Controls("Insert Picture").OnAction
= "CompInsert"
.Controls("Insert Picture").FaceId = 295
.Controls.Add(Type:=msoControlButton,
Befo=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
.

.

.