Cut button is disabled

[Asif]

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

 

[Equiangular]

How about reenable the buttons in Worksheet_Deactivate() ?

 

[Ron de Bruin]

Hi Asif

You must reset it in the Worksheet_Deactivate event

 

[Asif]

I have a similar routine re-enabling everything in the Worksheet_Deactivate
event. Still doesn't work.

--
Thanx & regards,
Asif

Hi Asif

You must reset it in the Worksheet_Deactivate event

 

[Asif]

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

I have a similar routine re-enabling everything in the Worksheet_Deactivate
event. Still doesn't work.

 

[Ron de Bruin]

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

I have a similar routine re-enabling everything in the Worksheet_Deactivate
event. Still doesn't work.

 

[Ron de Bruin]

Oops, the last one(Deactivate) must be True instead of false

 

[Asif]

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.

 

[Ron de Bruin]

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

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.

 

[Asif]

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.

 

[Ron de Bruin]

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

 

[Asif]

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.

 

[Tom Ogilvy]

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

 

[Ron de Bruin]

Remove the empty module(s)

See
http://www.contextures.com/xlfaqMac.html#NoMacros

 

[Asif]

Thank you... it worked.