Key event

[Greg]

How can I get a key press [down | up] event in Excel? Or,
how do I know if the delete key was pressed?

Thanks!

 

[TroyW]

Greg,

Have you taken a look at using the sheet protection command from the
Menubar. I'm not sure what version of Excel you have. The newer versions
allow a more granular control of protection including deletion of rows and
columns.

Alternatively, below is some code that uses Application.OnKey to trap the
Delete key. Test and see if it does what you want. You will need to call the
Initialize_DeleteTrap routine in a Workbook_Open event or something similar.

Troy

Sub Initialize_DeleteTrap()
Application.OnKey "{DEL}", "subDeletePressed"
End Sub

Sub subDeletePressed()
Dim rng As Range

If TypeName(Selection) = "Range" Then
Set rng = Selection
If rng.Address = rng.EntireRow.Address Then
MsgBox "Not allowed to delete entire row"
ElseIf rng.Address = rng.EntireColumn.Address Then
MsgBox "Not allowed to delete entire column"
Else
Application.OnKey "{DEL}"
SendKeys "{DEL}", True
Application.OnKey "{DEL}", "subDeletePressed"
End If
Else
Application.OnKey "{DEL}"
SendKeys "{DEL}", True
Application.OnKey "{DEL}", "subDeletePressed"
End If

Set rng = Nothing
End Sub

I want to let the user delete selected text/content in
selected cells. However, I want the user to be unable to
delete rows at a time... I hope that makes sense... I
don't want the user to be able to select a row(s) and hit
delete...

Thanks!

-----Original Message-----
Greg,

There is really no good way to do this. Perhaps if you describe
why you need to trap the Delete key, some one could come up with
a suitable workaround (e.g., the Change event procedure).


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

How can I get a key press [down | up] event in Excel? Or,
how do I know if the delete key was pressed?

Thanks!

.

 

[Tom Ogilvy]

Why not unlock the cells you will allow the user to alter, then protect the
worksheet.

--
Regards,
Tom Ogilvy

I want to let the user delete selected text/content in
selected cells. However, I want the user to be unable to
delete rows at a time... I hope that makes sense... I
don't want the user to be able to select a row(s) and hit
delete...

Thanks!

-----Original Message-----
Greg,

There is really no good way to do this. Perhaps if you describe
why you need to trap the Delete key, some one could come up with
a suitable workaround (e.g., the Change event procedure).


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

How can I get a key press [down | up] event in Excel? Or,
how do I know if the delete key was pressed?

Thanks!

.