Delete Key does not trigger change event

mecerrato

Board Regular
Joined
Oct 5, 2015
Messages
68
I have the following code in the worksheet change area and it works fine except for when I hit the delete key, it does not trigger.
I tested by using another cell and checking for the true result of =BH13=0 and it returns true so I know the value is 0 but when I hit the delete key but I am not sure why it doesn't execute the code.
BH13 does have a formula and I think that may have something to do with it but not sure what, this is the formula in BH13
=IFERROR(IF(BH12<>"pay periods=?",IF(BH12<>0,ROUNDDOWN(BH12/12,2),0),0),"")

any ideas on how I can fix this?

VBA Code:
If Target.Address = "$BH$10" Then
    If Range("$BH$13").Value = 0 Then
        ActiveSheet.Shapes("BaseIncome").Visible = False
    Else
    If Range("$BH$21").Value = "Declining" Then
        ActiveSheet.Shapes("IncomeDecline").Visible = True
        ActiveSheet.Shapes("BaseIncome").Visible = False
    Else
        ActiveSheet.Shapes("IncomeDecline").Visible = False
    If Range("$BH$20").Value > Range("$BH$13").Value Then
        ActiveSheet.Shapes("BaseIncome").Visible = True
    Else
        ActiveSheet.Shapes("BaseIncome").Visible = False
    End If
    End If
    End If
End If
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

mecerrato

Board Regular
Joined
Oct 5, 2015
Messages
68
I added the code described in the stackoverflow link but am stuck at the procedure part. I am an amateur VBA coder and only use it to simplify my business life.
where it says "Do Something" I am not sure what to put there

VBA Code:
Private Sub Worksheet_Activate()
        Application.OnKey Key:="{DEL}", Procedure:="DoSomething"
    End Sub
1615048435971.png
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,948
.
Paste this in the ThisWorkbook Module :

VBA Code:
Option Explicit

Private Sub Workbook_Open()
    Application.OnKey "{DELETE}", "MyMacro"
End Sub


Paste this in a Regular module :

Code:
Option Explicit

Sub mymacro()
    MsgBox "This is a test message. Replace this with your macro commands", vbInformation, "Testing"
    'Delete the MsgBox and replace with your macro code.
End Sub
 

mecerrato

Board Regular
Joined
Oct 5, 2015
Messages
68

ADVERTISEMENT

.
Paste this in the ThisWorkbook Module :

VBA Code:
Option Explicit

Private Sub Workbook_Open()
    Application.OnKey "{DELETE}", "MyMacro"
End Sub


Paste this in a Regular module :

Code:
Option Explicit

Sub mymacro()
    MsgBox "This is a test message. Replace this with your macro commands", vbInformation, "Testing"
    'Delete the MsgBox and replace with your macro code.
End Sub
The functionality works with the MsgBox but what is the code that I can put in the MyMacro procedure that will enter a 0 in the cell that I hit the delete key on?
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,948
VBA Code:
Sub mymacro()
    MsgBox "This is a test message. Replace this with your macro commands", vbInformation, "Testing"
    ActiveSheet.Range("A1").Value = 0
    
End Sub

Change the "A1" for the cell you are referencing.
 

mecerrato

Board Regular
Joined
Oct 5, 2015
Messages
68
VBA Code:
Sub mymacro()
    MsgBox "This is a test message. Replace this with your macro commands", vbInformation, "Testing"
    ActiveSheet.Range("A1").Value = 0
   
End Sub

Change the "A1" for the cell you are referencing.
Since this affects the whole sheet because we added to the workbook, I need to change the cell I am currently on so I cannot reference any particular cell, it has to be the current cell I am on (which can be different each time)
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,948
VBA Code:
Option Explicit

Sub mymacro()
    MsgBox "This is a test message. Replace this with your macro commands", vbInformation, "Testing"
    ActiveCell.Value = 0
    
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,126,928
Messages
5,621,642
Members
415,849
Latest member
PhoenixRising2015

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top