Disable Command Bar Control
 

I am trying to disable the "cut" control in the "edit" menu in the Command
Bar, as well as in any other menu where "cut" may appear. So far the below
code, which I input into the specific sheet's code window, works, but it does
not disable the key board shortcut "control x". That is but a minor problem.
The main issue is that the code disables the "cut" control in all of my
Excel files. Any ideas on how I could disable the keyboard short cut as well
make the code apply only to a particular sheet rather than to my Excel in
general? Any help is greatly appreciated.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Ctrl As Office.CommandBarControl
For Each Ctrl In Application.CommandBars.FindControls(ID:=21)
Ctrl.Enabled = False
Next Ctrl
End Sub

Disable Command Bar Control
 

hi iperlovsky

Use onkey
http://www.rondebruin.nl/key.htm

Use this two eventsto run your code

Private Sub Workbook_Activate()

End Sub

Private Sub Workbook_Deactivate()

End Sub


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"iperlovsky" wrote in message ...
I am trying to disable the "cut" control in the "edit" menu in the Command
Bar, as well as in any other menu where "cut" may appear. So far the below
code, which I input into the specific sheet's code window, works, but it does
not disable the key board shortcut "control x". That is but a minor problem.
The main issue is that the code disables the "cut" control in all of my
Excel files. Any ideas on how I could disable the keyboard short cut as well
make the code apply only to a particular sheet rather than to my Excel in
general? Any help is greatly appreciated.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Ctrl As Office.CommandBarControl
For Each Ctrl In Application.CommandBars.FindControls(ID:=21)
Ctrl.Enabled = False
Next Ctrl
End Sub

Disable Command Bar Control
 

Thanks for the link Ron. Changing the code to begin with "Private Sub
Workbook_Activate()" unfortunately does not eliminate my problem of limiting
the code to be active on only the selected sheet rather than my Excel in
general. What am I doing wrong?

"Ron de Bruin" wrote:

hi iperlovsky

Use onkey
http://www.rondebruin.nl/key.htm

Use this two eventsto run your code

Private Sub Workbook_Activate()

End Sub

Private Sub Workbook_Deactivate()

End Sub


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"iperlovsky" wrote in message ...
I am trying to disable the "cut" control in the "edit" menu in the Command
Bar, as well as in any other menu where "cut" may appear. So far the below
code, which I input into the specific sheet's code window, works, but it does
not disable the key board shortcut "control x". That is but a minor problem.
The main issue is that the code disables the "cut" control in all of my
Excel files. Any ideas on how I could disable the keyboard short cut as well
make the code apply only to a particular sheet rather than to my Excel in
general? Any help is greatly appreciated.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Ctrl As Office.CommandBarControl
For Each Ctrl In Application.CommandBars.FindControls(ID:=21)
Ctrl.Enabled = False
Next Ctrl
End Sub

Disable Command Bar Control
 

This are events that belong in the thisworkbook module
You call the macro in the event or put all code in the event

Private Sub Workbook_Activate()
Call macroname1
End Sub

Private Sub Workbook_Deactivate()
Call macroname2
End Sub




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"iperlovsky" wrote in message ...
Thanks for the link Ron. Changing the code to begin with "Private Sub
Workbook_Activate()" unfortunately does not eliminate my problem of limiting
the code to be active on only the selected sheet rather than my Excel in
general. What am I doing wrong?

"Ron de Bruin" wrote:

hi iperlovsky

Use onkey
http://www.rondebruin.nl/key.htm

Use this two eventsto run your code

Private Sub Workbook_Activate()

End Sub

Private Sub Workbook_Deactivate()

End Sub


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"iperlovsky" wrote in message ...
I am trying to disable the "cut" control in the "edit" menu in the Command
Bar, as well as in any other menu where "cut" may appear. So far the below
code, which I input into the specific sheet's code window, works, but it does
not disable the key board shortcut "control x". That is but a minor problem.
The main issue is that the code disables the "cut" control in all of my
Excel files. Any ideas on how I could disable the keyboard short cut as well
make the code apply only to a particular sheet rather than to my Excel in
general? Any help is greatly appreciated.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Ctrl As Office.CommandBarControl
For Each Ctrl In Application.CommandBars.FindControls(ID:=21)
Ctrl.Enabled = False
Next Ctrl
End Sub