how do I make a macro run on any key press

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
17,955
Office Version
2013
Platform
Windows
Can be done Dave.......but that would / could open a pandoras box of problems....what if you didn't want the macro to run, just wanted to type something into a cell ??
Might be easier to look at a Selection_change event
 
Last edited:

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
1,020
I tried to enter this in the worksheet change event but it didn't work.

Code:
Application.OnKey "{DELETE}", ThisWorkbook.Worksheets("CSS_quote_sheet").Range("C11").Formula = "=IF(A11="""","""",IF(COUNTIF(Sheet2!$G$87:$DO$97,A11),""Public_holiday"",IF(WEEKDAY(A11)=1,""Sun"",IF(WEEKDAY(A11)=7,""Sat"",""Business_day_rate""))))"
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
17,955
Office Version
2013
Platform
Windows
Post ALL the code and did you put in the sheet module ??
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
1,020
I can't post all of the code for privacy issues.

The start of the procedure looks like this:

The only bit I added to the on change event was the last line
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.OnKey "{DELETE}", ThisWorkbook.Worksheets("CSS_quote_sheet").Range("C11").Formula = "=IF(A11="""","""",IF(COUNTIF(Sheet2!$G$87:$DO$97,A11),""Public_holiday"",IF(WEEKDAY(A11)=1,""Sun"",IF(WEEKDAY(A11)=7,""Sat"",""Business_day_rate""))))"
 
Last edited:

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
17,955
Office Version
2013
Platform
Windows
Ok, so why do you need a delete ON Key.
Why not simply use the cell location, so when the cell is cleared, this code runs
Change the text in red to your cell

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("[color=red]YOUR CELL[/color]")) Is Nothing Then Exit Sub
Worksheets("CSS_quote_sheet").Range("C11").Formula = "=IF(A11="""","""",IF(COUNTIF(Sheet2!$G$87:$DO$97,A11),""Public_holiday"",IF(WEEKDAY(A11)=1,""Sun"",IF(WEEKDAY(A11)=7,""Sat"",""Business_day_rate""))))"
End Sub
 
Last edited:

Forum statistics

Threads
1,077,649
Messages
5,335,473
Members
399,017
Latest member
npatel917

Some videos you may like

This Week's Hot Topics

Top