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.


"George Raymond" wrote in message
...
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

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


George Raymond wrote in message
...
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

would you look at that! learn something new every day...


"Tom Ogilvy" wrote in 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


George Raymond wrote in message
...
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

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


George Raymond
wrote in message
...
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



.