Disable the Edit menu command 'Paste Special'

  • Thread starter Thread starter George Raymond
  • Start date Start date
G

George Raymond

Is there a way to disable the Edit menu command "Paste
Special" while users opening and using a workbook I
supply? And then enable the command back again on exit?

I tried to do it by adding two events
Delete the command from the Edit menu with a Private Sub
Workbook-Open() event and Add the command back using the
Workbook-BeforeClose event. However, that is not very
useful because the user simply use the Customize option to
add 'Paste Special' back and use it. It defeats the
purpose.

Please help!

George
 
I'm not aware of a way to prevent users from customising their commandbars.
I don't think there is a way.
 
you can set the protection property of acommandbar

Application.CommandBars("Worksheet Menu Bar").Protection =

here is a routine that shows the constants. You add them to get the
combination you want (basically they are setting the bits in a binary word).
The default value is 8. As an example
You probably want to at no customization to the Edit commandbar:

Application.CommandBars("Edit").Protection =

the default is 22 = 16 + 4 + 2

msoBarNoResize 2
msoBarNoMove 4
msoBarNoChangeDock 16

You probably want to add
msoBarNoCustomize 1

Sub tester2()
varr = Array("msoBarNoProtection ", _
"msoBarNoCustomize ", _
"msoBarNoResize ", _
"msoBarNoMove ", _
"msoBarNoChangeVisible", _
"msoBarNoChangeDock ", _
"msoBarNoVerticalDock ", _
"msoBarNoHorizontalDock")
varr1 = Array(msoBarNoProtection, _
msoBarNoCustomize, _
msoBarNoResize, _
msoBarNoMove, _
msoBarNoChangeVisible, _
msoBarNoChangeDock, _
msoBarNoVerticalDock, _
msoBarNoHorizontalDock)

For i = LBound(varr) To UBound(varr)
Debug.Print i, varr(i), varr1(i)
Next
End Sub
 
Thank you Tom - That's great
George
-----Original Message-----
you can set the protection property of acommandbar

Application.CommandBars("Worksheet Menu Bar").Protection =

here is a routine that shows the constants. You add them to get the
combination you want (basically they are setting the bits in a binary word).
The default value is 8. As an example
You probably want to at no customization to the Edit commandbar:

Application.CommandBars("Edit").Protection =

the default is 22 = 16 + 4 + 2

msoBarNoResize 2
msoBarNoMove 4
msoBarNoChangeDock 16

You probably want to add
msoBarNoCustomize 1

Sub tester2()
varr = Array("msoBarNoProtection ", _
"msoBarNoCustomize ", _
"msoBarNoResize ", _
"msoBarNoMove ", _
"msoBarNoChangeVisible", _
"msoBarNoChangeDock ", _
"msoBarNoVerticalDock ", _
"msoBarNoHorizontalDock")
varr1 = Array(msoBarNoProtection, _
msoBarNoCustomize, _
msoBarNoResize, _
msoBarNoMove, _
msoBarNoChangeVisible, _
msoBarNoChangeDock, _
msoBarNoVerticalDock, _
msoBarNoHorizontalDock)

For i = LBound(varr) To UBound(varr)
Debug.Print i, varr(i), varr1(i)
Next
End Sub

--
Regards,
Tom Ogilvy





.
Click to expand...
 
Back
Top