Trap Keys

  • Thread starter Thread starter BillJunior
  • Start date Start date
B

BillJunior

I have the following code in my Worksheet_Change macro
area. Basically it is designed so that if someone is in
column A

between row 2 and 30 that it will delete the 3 cells in
column B, C and D on the same row that the person has
selected a cell

in column A and hit delete.

If ActiveCell.Row > 1 And ActiveCell.Row < 31 Then
If ActiveCell.Column = 1 Then
If ActiveCell.Value = "" Then
ActiveCell.Range("A1:D1").Select
Selection.Interior.ColorIndex = xlNone
Selection.ClearContents

Now my issue is that it seems to run in a loop
approximately 30-40 times at least when someone deletes
the item in column A.

So one of two things, is there either:
A. A way to trap which key was pressed to test and do
something different if it is the delete key.
B. Some way to force the macro not to run again when the
ClearContents command is issued inside the macro (which
in turn triggers another Worksheet_Change function).

Thanks

BillJunior
 
BillJunior

One way is to disable event handling.
Something like this:

Sub ...............

On Error Goto Finito

Application.EnableEvents = False

Do your stuff

Finito:

Application.EnableEvents = True
End Sub
 
Wow, that worked perfectly! Thanks for the tip!
-----Original Message-----
BillJunior

One way is to disable event handling.
Something like this:

Sub ...............

On Error Goto Finito

Application.EnableEvents = False

Do your stuff

Finito:

Application.EnableEvents = True
End Sub


--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"BillJunior" <[email protected]> skrev i en meddelelse



.
Click to expand...
 
Back
Top