CommandBars(33)

[CoRrRan]

I have got a slight problem:

I want to change the contextmenu 33 (=CommandBars(33)-object) of Excel
2000. This is the menu one gets after click and dragging a range of
cells with the right mouse button and releasing the button.

I have created the menu-item in the following manner:

**************************
Sub CreateButton()

Dim newBttn1 As Object

Set newBttn1 = CommandBars(33).Controls.add(Type:=msoControlButton, _
before:=5)
With newBttn1
.Style = msoButtonCaption
.OnAction = "PasteFormulas"
.Caption = "Copy Here as Formulas Only"
End With

End Sub
**************************

Within my PERSONAL.XLS I have created the following procedure:

**************************
Sub PasteFormulas()
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End Sub
**************************

But, this does not work... Excel returns the following error:
"PasteSpecial method of Range class Failed".

Does any1 have a solution to this little problem?

TIA,
CoRrRan

 

[Guest]

Without seeing the wbk I can't tell, but a few things which will cause a
Paste to fail:

- Pasting onto cells that are locked
- trying to paste when a portion of the range "runs off the sheet"
Are you able to do the copy/paste manually, without vba?
Do you get an error this way?

 

[CoRrRan]

Without seeing the wbk I can't tell, but a few things which will cause
a Paste to fail:

- Pasting onto cells that are locked
- trying to paste when a portion of the range "runs off the sheet"
Are you able to do the copy/paste manually, without vba?
Do you get an error this way?

Yes, copy and pasting manually works perfect. I think that Excel is
somehow using a different method of copy and pasting the data into the
new range if the range is selected and copied (or moved) using the cell
or range border method.

CoRrRan