EXCEl - Single-key - increment day and re-calc.

[TheBramble]

I have an Excel spreadsheet displaying data for an entire calendar year. Depending upon the actual day of the year, the calculation section will profile certain characteristics. It also uses conditional formatting to highlight within the raw data where there are matches +/- certain values against this profile section. All of this work is complete.

What I would like to be able to do is to press one key (such as F9 to recalc), but each time it’s pressed, the day of the year increments by one day so that I can get a dynamic display of the changing profile as it whizzes through the year.

I presume there is a visbasic or some such method to do this and wondered if any Excel guru out there could suggest a solution.

 

[A Dashing Blade]

You'll need to check out the "Application.OnKey" command

Basically in your workbook_open event will look something like this . . .

Private Sub Workbook_Open()

Application.OnKey "^q", "IncrementDate"

End Sub

ie When I press "Control q" run the macro called IncrementDate

note that you should clear this via the Worksheet_Close event ie

Private Sub Workbook_Close()

Application.OnKey "^q", ""

End Sub

then in a normal code module . . .

In your macro that increments time, you'll need to increment a module leval variable as you'll want to reset back to zero


'Module Variable
Dim DateCounter as integer

Public Sub IncrementDate

DateCounter = DateCounter + 1
Range("NumberOfDaysToIncrement").value = DateCounter

End Sub

'call this sub via another keystroke (?)
Public sub Resetcounter

DateCounter = 0
Range("NumberOfDaysToIncrement").value = DateCounter

End Sub

Obviously lots of ways of incrementing, but the point is the .OnKey method

 

[TheBramble]

I have to admit, I haven't really understood a thing. I'll get hold of VBA or EXCEL for dummies and come back and have another read.

Thanks for your inputs both - whatever they mean....

(Actually, this is one of those rare and valuable moments when you know you don't know, but you know you're going to know).

 

[A Dashing Blade]

Happy to try and help by PM or something mate . . .

Ditto, please don't hesitate etc

 

[A Dashing Blade]

Pressed "Publish" button twice