how do I make a macro run on any key press

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,448
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,216
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
18,448
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,216
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
18,448
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:

Watch MrExcel Video

Forum statistics

Threads
1,090,363
Messages
5,414,009
Members
403,511
Latest member
Emmanuel John

This Week's Hot Topics

Top