| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cut button is disabled
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 -- Thanx & regards, Asif |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cut button is disabled
How about reenable the buttons in Worksheet_Deactivate() ?
Asif wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cut button is disabled
Hi Asif
You must reset it in the Worksheet_Deactivate event -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Asif" wrote in message ... 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 -- Thanx & regards, Asif |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cut button is disabled
I have a similar routine re-enabling everything in the Worksheet_Deactivate
event. Still doesn't work. -- Thanx & regards, Asif "Ron de Bruin" wrote: Hi Asif You must reset it in the Worksheet_Deactivate event -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Asif" wrote in message ... 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 -- Thanx & regards, Asif |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cut button is disabled
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" wrote: I have a similar routine re-enabling everything in the Worksheet_Deactivate event. Still doesn't work. -- Thanx & regards, Asif "Ron de Bruin" wrote: Hi Asif You must reset it in the Worksheet_Deactivate event -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Asif" wrote in message ... 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 -- Thanx & regards, Asif |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cut button is disabled
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" wrote in message ... I have a similar routine re-enabling everything in the Worksheet_Deactivate event. Still doesn't work. -- Thanx & regards, Asif "Ron de Bruin" wrote: Hi Asif You must reset it in the Worksheet_Deactivate event -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Asif" wrote in message ... 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 -- Thanx & regards, Asif |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cut button is disabled
Oops, the last one(Deactivate) must be True instead of false
-- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... 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" wrote in message ... I have a similar routine re-enabling everything in the Worksheet_Deactivate event. Still doesn't work. -- Thanx & regards, Asif "Ron de Bruin" wrote: Hi Asif You must reset it in the Worksheet_Deactivate event -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Asif" wrote in message ... 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 -- Thanx & regards, Asif |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cut button is disabled
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. -- Thanx & regards, Asif "Ron de Bruin" wrote: Oops, the last one(Deactivate) must be True instead of false -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... 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" wrote in message ... I have a similar routine re-enabling everything in the Worksheet_Deactivate event. Still doesn't work. -- Thanx & regards, Asif "Ron de Bruin" wrote: Hi Asif You must reset it in the Worksheet_Deactivate event -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Asif" wrote in message ... 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 -- Thanx & regards, Asif |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cut button is disabled
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" wrote in message ... 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. -- Thanx & regards, Asif "Ron de Bruin" wrote: Oops, the last one(Deactivate) must be True instead of false -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... 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" wrote in message ... I have a similar routine re-enabling everything in the Worksheet_Deactivate event. Still doesn't work. -- Thanx & regards, Asif "Ron de Bruin" wrote: Hi Asif You must reset it in the Worksheet_Deactivate event -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Asif" wrote in message ... 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 -- Thanx & regards, Asif |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cut button is disabled
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. -- Thanx & regards, Asif "Ron de Bruin" wrote: 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" wrote in message ... 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. -- Thanx & regards, Asif "Ron de Bruin" wrote: Oops, the last one(Deactivate) must be True instead of false -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... 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" wrote in message ... I have a similar routine re-enabling everything in the Worksheet_Deactivate event. Still doesn't work. -- Thanx & regards, Asif "Ron de Bruin" wrote: Hi Asif You must reset it in the Worksheet_Deactivate event -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Asif" wrote in message ... 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 -- Thanx & regards, Asif |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cut button is disabled
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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Asif" wrote in message ... 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. -- Thanx & regards, Asif "Ron de Bruin" wrote: 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" wrote in message ... 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. -- Thanx & regards, Asif "Ron de Bruin" wrote: Oops, the last one(Deactivate) must be True instead of false -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... 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" wrote in message ... I have a similar routine re-enabling everything in the Worksheet_Deactivate event. Still doesn't work. -- Thanx & regards, Asif "Ron de Bruin" wrote: Hi Asif You must reset it in the Worksheet_Deactivate event -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Asif" wrote in message ... 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 -- Thanx & regards, Asif |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cut button is disabled
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. -- Thanx & regards, Asif "Ron de Bruin" wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Asif" wrote in message ... 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. -- Thanx & regards, Asif "Ron de Bruin" wrote: 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" wrote in message ... 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. -- Thanx & regards, Asif "Ron de Bruin" wrote: Oops, the last one(Deactivate) must be True instead of false -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... 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" wrote in message ... I have a similar routine re-enabling everything in the Worksheet_Deactivate event. Still doesn't work. -- Thanx & regards, Asif "Ron de Bruin" wrote: Hi Asif You must reset it in the Worksheet_Deactivate event -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Asif" wrote in message ... 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 -- Thanx & regards, Asif |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cut button is disabled
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 -- Regards, Tom Ogilvy "Asif" wrote: 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. -- Thanx & regards, Asif "Ron de Bruin" wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Asif" wrote in message ... 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. -- Thanx & regards, Asif "Ron de Bruin" wrote: 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" wrote in message ... 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. -- Thanx & regards, Asif "Ron de Bruin" wrote: Oops, the last one(Deactivate) must be True instead of false -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... 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" wrote in message ... I have a similar routine re-enabling everything in the Worksheet_Deactivate event. Still doesn't work. -- Thanx & regards, Asif "Ron de Bruin" wrote: Hi Asif You must reset it in the Worksheet_Deactivate event -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Asif" wrote in message ... 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 -- Thanx & regards, Asif |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cut button is disabled
Remove the empty module(s)
See http://www.contextures.com/xlfaqMac.html#NoMacros -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Asif" wrote in message ... 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. -- Thanx & regards, Asif "Ron de Bruin" wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Asif" wrote in message ... 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. -- Thanx & regards, Asif "Ron de Bruin" wrote: 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" wrote in message ... 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. -- Thanx & regards, Asif "Ron de Bruin" wrote: Oops, the last one(Deactivate) must be True instead of false -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... 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" wrote in message ... I have a similar routine re-enabling everything in the Worksheet_Deactivate event. Still doesn't work. -- Thanx & regards, Asif "Ron de Bruin" wrote: Hi Asif You must reset it in the Worksheet_Deactivate event -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Asif" wrote in message ... 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 -- Thanx & regards, Asif |
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cut button is disabled
Thank you... it worked.
-- Thanx & regards, Asif "Ron de Bruin" wrote: Remove the empty module(s) See http://www.contextures.com/xlfaqMac.html#NoMacros -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Asif" wrote in message ... 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. -- Thanx & regards, Asif "Ron de Bruin" wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Asif" wrote in message ... 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. -- Thanx & regards, Asif "Ron de Bruin" wrote: 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" wrote in message ... 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. -- Thanx & regards, Asif "Ron de Bruin" wrote: Oops, the last one(Deactivate) must be True instead of false -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... 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" wrote in message ... I have a similar routine re-enabling everything in the Worksheet_Deactivate event. Still doesn't work. -- Thanx & regards, Asif "Ron de Bruin" wrote: Hi Asif You must reset it in the Worksheet_Deactivate event -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Asif" wrote in message ... 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 -- Thanx & regards, Asif |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Attach button disabled | Excel Discussion (Misc queries) | |||
| Blog Category button disabled | Excel Discussion (Misc queries) | |||
| option button disabled | Excel Programming | |||
| Why Is The Macro Edit Button Disabled? | Excel Programming | |||
| Can the X to Close button on a workbook be disabled? | Excel Programming | |||
Linear Mode
