Disable Fill Down, Right, Up....

  • Thread starter Thread starter Verena
  • Start date Start date
V

Verena

Alas, I am stuck with having users fill out Excel forms to gather
change data. In my blissful ignorance, I validated the cells for dates
and such, so they could only enter what I wanted. However, they have
thwarted me by cutting and pasting incorrect data into my validated
cells.

I figured out how to keep them from pasting into my cells, but am at a
loss to how to disable the Fill commands. I want them all disabled;
up, down, right, left - all of them. Ultimately, I'd like an electric
shock to emanate from the keyboard when they enter something
incorrectly, but I'll settle for disabling the fill function.

Please help.

Thanks,
Verena
 
hi,
try this...
CommandBars(1).Controls("Edit").Controls("Fill").Visible =
False

peter
 
Ooh, this didn't work so good. It does make the option disappear from
the menu, but does not inhibit Ctrl-D. It also seemed to affect Excel
permanently, I had to reset the menu before it would re-appear in any
workbook.
 
Hi,
You could put that line of code in a sub so that when they
open the book it would run, then, you could put the reset
(=true) in a sub that runs when they close. They should be
instructed not to run other books while running your book
because you have taken control of the invironment.
To diable the control keys you could run the following
code...
Dim KilledKeys As Variant

Sub KillKeys()
'' + = Shift ^ = Ctrl % = Alt
KilledKeys = Array("+{F1}", "%{F1}", "+{F2}", "^ {F2}", "{F3}", _
"^{F3}", "+{F3}", "{F4}", "+{F4}", "^ {F4}", "{F5}", "+{F5}", _
"^{F6}", "{F7}", "^{F7}", "{F8}", "^{F8}", "+ {F8}", "{F9}", "^{F9}", _
"+{F9}", "^{F10}", "{F11}", "^{F11}", "+ {F11}", "{F12}", "^{F12}", _
"+{F12}", "^{PgDn}", "^{PgUp}")
SetKeysSub KilledKeys, ""
End Sub

Sub SetKeysSub(KeyArray, Optional AssignedMacro As Variant)
Dim Counter As Integer
If IsArray(KeyArray) Then
For Counter = 0 To UBound(KeyArray)
If IsMissing(AssignedMacro) Then
Application.OnKey KeyArray(Counter)
Else
Application.OnKey KeyArray(Counter), AssignedMacro
End If
Next
Else
If IsMissing(AssignedMacro) Then
Application.OnKey KeyArray
Else
Application.OnKey KeyArray, AssignedMacro
End If
End If

Sub ResetKilledKeys()
SetKeysSub KilledKeys
End Sub
Click to expand...

Hope this helps

peter
 
Back
Top