Cut button is disabled

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have used following code to disable cut in a worksheet. But remains
disabled permanently for the workbook and even for other workbooks. I'd have
to re-start Excel to get it enabled. Is there a workaround... please help.

Private Sub Worksheet_Activate()
Dim eCtrl As CommandBarControl
On Error Resume Next
For Each eCtrl In Application.CommandBars
eCtrl.FindControls(ID:=21).Enabled = 0 'Disable Ctrl-x
eCtrl.FindControls(ID:=1964).Enabled = 0 'Disable Clear - All
eCtrl.FindControls(ID:=872).Enabled = 0 'Disable Clear - Formats
Next eCtrl
With Application
If .CutCopyMode = xlCut Then .CutCopyMode = False 'Clear clipboard
.OnKey "^x", ""
.CellDragAndDrop = False
.CopyObjectsWithCells = False
End With
End Sub
 
I have a similar routine re-enabling everything in the Worksheet_Deactivate
event. Still doesn't work.

--
Thanx & regards,
Asif


Ron de Bruin said:
Hi Asif

You must reset it in the Worksheet_Deactivate event
Click to expand...
 
The cut button remains permanently disabled but I can use ctrl-x in other
worksheets of the same workbook. The cut button gets restored after re-start.
--
Thanx & regards,
Asif


Asif said:
I have a similar routine re-enabling everything in the Worksheet_Deactivate
event. Still doesn't work.
Click to expand...
 
There are more problems with your code

To disable Cut for example in all toolbars use

Private Sub Worksheet_Activate()
' Excel 2000 - 2003
Dim Ctrl As Office.CommandBarControl
For Each Ctrl In Application.CommandBars.FindControls(ID:=21)
Ctrl.Enabled = False
Next Ctrl
End Sub


Private Sub Worksheet_Deactivate()
' Excel 2000 - 2003
Dim Ctrl As Office.CommandBarControl
For Each Ctrl In Application.CommandBars.FindControls(ID:=21)
Ctrl.Enabled = False
Next Ctrl
End Sub


--

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


Asif said:
I have a similar routine re-enabling everything in the Worksheet_Deactivate
event. Still doesn't work.
Click to expand...
 
Hi Ron,

I do not want to disable ctrl-x for all office. I just want to do so in one
particular worksheet. Also, any idea why i can't disable clear in the code
below.
 
If you copy the two events I posted in a worksheet module you see that it will work only for that worksheet
Is it working for you?????????

From which Toolbar do you want to disable the items


--

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


Asif said:
Hi Ron,

I do not want to disable ctrl-x for all office. I just want to do so in one
particular worksheet. Also, any idea why i can't disable clear in the code
below.
Click to expand...
 
I'm using Excel 2003. Before the "Cut" in Edit menu was getting disabled. Now
it remains enabled. The cut button on Standard bar remains disabled though.
 
This is working OK in 2003 for Cut if you copy it in a sheet module
http://www.rondebruin.nl/code.htm

Test it again

Private Sub Worksheet_Activate()
' Excel 2000 - 2003
Dim Ctrl As Office.CommandBarControl
For Each Ctrl In Application.CommandBars.FindControls(ID:=21)
Ctrl.Enabled = False
Next Ctrl
End Sub


Private Sub Worksheet_Deactivate()
' Excel 2000 - 2003
Dim Ctrl As Office.CommandBarControl
For Each Ctrl In Application.CommandBars.FindControls(ID:=21)
Ctrl.Enabled = True
Next Ctrl
End Sub
 
Thank you very much. It's working now.

P.s. Any idea how I can remove macro warning for workbooks which don't
contain any maro but yet a warning message is shown. I saved as the file
which contained macro in "worksheet" not in "workbook". I removed the sheet
that contained the code but I still get the warning message.
 
delete any standard module even if empty (shown in the project explorer)

go to each module associated with a worksheet or the thisworkbook module,
click in it, do Ctrl+A, hit delete , close it

Save the workbook
 
Back
Top