Disabling items on the paste special menu

kriccio

New Member
Joined
Mar 5, 2002
Messages
3
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.
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top