how do I make a macro run on any key press

[dpaton05]

How do I get vba code to run if any key if pressed?

 

[Michael M]

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

 

[dpaton05]

I tried to enter this in the worksheet change event but it didn't work.

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]

Post ALL the code and did you put in the sheet module ??

 

[dpaton05]

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

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""))))"

 

[Michael M]

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

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

 

[dpaton05]

Just realised I don't need this anymore.

 

[Michael M]

Just realised I don't need this anymore.

Thats probably the best outcome...