Disabling items on the paste special menu

[kriccio]

Can anyone tell me what the macro codes would be to disable all of the items on the paste special menu except the Values selection. When users cut and paste they overwrite my conditional formats.

[Mr Nick]

Do you really need to keep the single option?
The easiest way would be to remove Paste Special completely:


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("Cell").Reset
End Sub

Private Sub Workbook_Open()
Application.CommandBars("Cell").Controls("Paste Special...").Delete
End Sub

[kriccio]

On 2002-04-29 15:40, Mr Nick wrote:
Do you really need to keep the single option?
The easiest way would be to remove Paste Special completely:


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("Cell").Reset
End Sub

Private Sub Workbook_Open()
Application.CommandBars("Cell").Controls("Paste Special...").Delete
End Sub

Thanks for your reply.
Unfortunately the users demand a copy and paste function. I only want to protect my conditional formats so I thought that only giving the users a paste of only the values from the paste special would be the way to go. I already had all copy and paste functions removed and it almost caused a riot. If you have any other information that would be helpful please let me know.


Thanks

[jhanasik]

I have the same issue. I can get to "paste special" within the code but I can't figure out how to disable the radio controls. Anyone here have any idea...This is what I have so far

Set X = CommandBars("Worksheet Menu Bar").Controls("Edit").Controls("Paste &Special...")
X.Controls("&Formulas").Checked = False

but the checked = false blows up.

[Ricky Morris]

On 2002-04-29 15:29, kriccio wrote:
Can anyone tell me what the macro codes would be to disable all of the items on the paste special menu except the Values selection. When users cut and paste they overwrite my conditional formats.

I think you would have to create a user form with only the values selection then call up that user form when Paste Special is clicked.

[MT_Shanachie]

I'm not sure about how to disable the paste and paste special functions, but once you do that you could add the Paste Values function to the cell shortcut menu like this:

Dim CBarItem As Object
Set CBarItem = Application.CommandBars("Cell").Controls.Add(msoControlButton, 370, , 5, True)

An incomplete answer, but I hope it helps.