Disable a new paste button

[Guest]

Hi,

I've created a new button for my toolbar - 'Paste Formulas'.
Code behind it is:
If Application.CutCopyMode = False Then
Exit Sub
Else
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
End If

When Excel is not in cutcopymode, the regular paste button is disabled. Is
there any way I can do this for my paste formulas button too?

I'd rather not do this on a 'worksheet_change' sub or anything that will
slow up excel.

Any tips would be appreciated,

Basil

 

[Sandy]

See if this works.

If Application.CutCopyMode = False Then
Application.CommandBars("What bar it's on"). _
Controls.Item("Your button").Enabled = False
Exit Sub
Else
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
End If


Sandy

 

[Guest]

Thanks Sandy. Nothing wrong with the code, but this will only disable it
after it is clicked.

I want it to behave exactly the same as the paste button (i.e. is disabled
all the time unless cutcopymode is true).

Is there a way that I can get Excel to run the code only when something is
copied into the clipboard?

Thanks.

B

 

[NickHK]

Basil,
You can add code to the top level menu to check the value of
Application.CutCopyMode and toggle the .Enabled property of your lower menu
item accordingly
This code must run in order to create the menu tree, so check then.

MainMenu.OnAction = "MainMenu_Click"

Public Function MainMenu_Click()
MsgBox Application.CutCopyMode
'Set the .Enabled property of your "Paste Formulas" menu item accordingly
End Function

NickHK