Solved: The Dreaded #REF!

[CaptRon]

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.

[vba]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[/vba]

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

[lucas]

You've entered the twilight zone Cap. I use userforms to completly avoid this kind of problem......user never gets to touch the sheets.

[GTO]

...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

Hi Cap'

I certainly agree w/Steve, if you can keep 'em off the sheet, that would seem best. If not...

To just disable drag-and-drop, Application.CellDragAndDrop = False Of course the user can just turn it right back on... and I don't know the control name/number to disable.

Mostly I thought to respond to the disabling of copy/paste including the shortcut key combo. Here's a link to a fairly recent thread - at post #4 I have the links to a kb entry by Ken Puhls as well as some updating done (if my memory serves). Anyways you could see if these help.

http://www.vbaexpress.com/forum/showthread.php?t=23460

http://www.vbaexpress.com/kb/getarticle.php?kb_id=373

http://www.vbaexpress.com/forum/showthread.php?t=19391

Mark

[mdmackillop]

Hi Ron,
Can you not just assign it to another macro?
[VBA]Sub test()
MsgBox "Control C disabled"
End Sub
[/VBA]

[CaptRon]

Thanks one an all for your assistance. I think I can manage with the information you've provided.

Steve, I'm not sure I'm up to building a userform with well over 200 data entry fields. User has to be able to draw a diagram on the report as well. I've employed userforms occasionally in the past, usually to provide a means of access or navigation, but never the whole enchilada.

I appreciate all your help.

Ron