CaptRon
02-17-2009, 04:27 PM
I use this code to disable several tools/options to limit the actions available to the user. My initial aim was to keep the user from cutting a cell and pasting it elsewhere and thus creating a #REF! error in a dependant formula. I still haven't figured out how to disable the keyboard shortcut Ctrl+c.
Sub EnableControls()
'Called when workbook deactivated to restore full edit capability
On Error Resume Next
With Application
With .CommandBars("Worksheet Menu Bar")
.Controls("File").Controls("Save As...").Enabled = True
.Controls("Edit").Controls("Cut").Enabled = True
.Controls("Edit").Controls("Delete...").Enabled = True
.Controls("Edit").Controls("Delete Sheet").Enabled = True
.Controls("Insert").Controls("Cells...").Enabled = True
.Controls("Insert").Controls("Rows").Enabled = True
.Controls("Insert").Controls("Columns").Enabled = True
.Controls("Format").Controls("Sheet").Enabled = True
End With
With .CommandBars("Ply")
.Controls("Delete").Enabled = True
.Controls("Rename").Enabled = True
End With
With .CommandBars("Standard")
.Controls("Save As...").Enabled = True
.Controls("Cut").Enabled = True
.Controls("Delete").Enabled = True
.Controls("Delete Sheet").Enabled = True
.Controls("Cells").Enabled = True
.Controls("Rows").Enabled = True
.Controls("Columns").Enabled = True
End With
End With
End Sub
Sub DisableControls()
'Called when workbook activated to limit user edit capability
On Error Resume Next
With Application
With .CommandBars("Worksheet Menu Bar")
.Controls("File").Controls("Save As...").Enabled = False
.Controls("Edit").Controls("Cut").Enabled = False
.Controls("Edit").Controls("Delete...").Enabled = False
.Controls("Edit").Controls("Delete Sheet").Enabled = False
.Controls("Insert").Controls("Cells...").Enabled = False
.Controls("Insert").Controls("Rows").Enabled = False
.Controls("Insert").Controls("Columns").Enabled = False
.Controls("Format").Controls("Sheet").Enabled = False
End With
With .CommandBars("Ply")
.Controls("Delete").Enabled = False
.Controls("Rename").Enabled = False
End With
With Application.CommandBars("Standard")
.Controls("Save As...").Enabled = False
.Controls("Cut").Enabled = False
.Controls("Delete").Enabled = False
.Controls("Delete Sheet").Enabled = False
.Controls("Cells").Enabled = False
.Controls("Rows").Enabled = False
.Controls("Columns").Enabled = False
End With
End With
'For OAI staff and administrator
If ThisWorkbook.Sheets("Start").Range("DF6").Value > 1 Then
With Application
.CommandBars("Worksheet Menu Bar").Controls("File") _
.Controls("Save As...").Enabled = True
.CommandBars("Standard").Controls("Save As...").Enabled = True
End With
End If
End Sub
The problem now remains that the user can MOVE a cell to another by using the drag & drop method and this also creates #REF! in the formula containing the cell reference.
Does anyone know how to disable moving a cell to another cell position?
Anyone know how to disable Ctrl+c?
Thanks for your help.
Ron
Sub EnableControls()
'Called when workbook deactivated to restore full edit capability
On Error Resume Next
With Application
With .CommandBars("Worksheet Menu Bar")
.Controls("File").Controls("Save As...").Enabled = True
.Controls("Edit").Controls("Cut").Enabled = True
.Controls("Edit").Controls("Delete...").Enabled = True
.Controls("Edit").Controls("Delete Sheet").Enabled = True
.Controls("Insert").Controls("Cells...").Enabled = True
.Controls("Insert").Controls("Rows").Enabled = True
.Controls("Insert").Controls("Columns").Enabled = True
.Controls("Format").Controls("Sheet").Enabled = True
End With
With .CommandBars("Ply")
.Controls("Delete").Enabled = True
.Controls("Rename").Enabled = True
End With
With .CommandBars("Standard")
.Controls("Save As...").Enabled = True
.Controls("Cut").Enabled = True
.Controls("Delete").Enabled = True
.Controls("Delete Sheet").Enabled = True
.Controls("Cells").Enabled = True
.Controls("Rows").Enabled = True
.Controls("Columns").Enabled = True
End With
End With
End Sub
Sub DisableControls()
'Called when workbook activated to limit user edit capability
On Error Resume Next
With Application
With .CommandBars("Worksheet Menu Bar")
.Controls("File").Controls("Save As...").Enabled = False
.Controls("Edit").Controls("Cut").Enabled = False
.Controls("Edit").Controls("Delete...").Enabled = False
.Controls("Edit").Controls("Delete Sheet").Enabled = False
.Controls("Insert").Controls("Cells...").Enabled = False
.Controls("Insert").Controls("Rows").Enabled = False
.Controls("Insert").Controls("Columns").Enabled = False
.Controls("Format").Controls("Sheet").Enabled = False
End With
With .CommandBars("Ply")
.Controls("Delete").Enabled = False
.Controls("Rename").Enabled = False
End With
With Application.CommandBars("Standard")
.Controls("Save As...").Enabled = False
.Controls("Cut").Enabled = False
.Controls("Delete").Enabled = False
.Controls("Delete Sheet").Enabled = False
.Controls("Cells").Enabled = False
.Controls("Rows").Enabled = False
.Controls("Columns").Enabled = False
End With
End With
'For OAI staff and administrator
If ThisWorkbook.Sheets("Start").Range("DF6").Value > 1 Then
With Application
.CommandBars("Worksheet Menu Bar").Controls("File") _
.Controls("Save As...").Enabled = True
.CommandBars("Standard").Controls("Save As...").Enabled = True
End With
End If
End Sub
The problem now remains that the user can MOVE a cell to another by using the drag & drop method and this also creates #REF! in the formula containing the cell reference.
Does anyone know how to disable moving a cell to another cell position?
Anyone know how to disable Ctrl+c?
Thanks for your help.
Ron