Changing Function to use a Date Input

[robertguy]

Hi.

can anyone please advise me on how to change the function keys.. sa
F10, F11 & F12 to use to input a date into a cell.

EG.

F10 to be 01/01/04

F11 to be 02/01/04

F12 to be 03/01/04

What I require is to go to any cell and press the relevant function ke
to Input the associated date


Any help would be greatly appreciate

Many thank

Rob

PS using Excel 200

 

[Dave Peterson]

First, I'm not sure if you entered Jan 1, 2004, Feb 1, 2004, and Mar 1, 2004 or
Jan 1st-3rd, 2004.

Here are a couple of macros. The first turns this on, the second turns it off.
The last 3 do the work:

Option Explicit
Sub EnableShortCuts()
Application.OnKey "{F10}", "doDate1"
Application.OnKey "{F11}", "doDate2"
Application.OnKey "{F12}", "doDate2"
End Sub
Sub ResetShortCuts()
Application.OnKey "{F10}"
Application.OnKey "{F11}"
Application.OnKey "{F12}"
End Sub
Sub doDate1()
On Error Resume Next
Selection.Value = DateSerial(2004, 1, 1)
If Err.Number <> 0 Then
MsgBox "Something went wrong!"
Err.Clear
End If
On Error GoTo 0
End Sub
Sub doDate2()
On Error Resume Next
Selection.Value = DateSerial(2004, 2, 1)
If Err.Number <> 0 Then
MsgBox "Something went wrong!"
Err.Clear
End If
On Error GoTo 0
End Sub
Sub doDate3()
On Error Resume Next
Selection.Value = DateSerial(2004, 3, 1)
If Err.Number <> 0 Then
MsgBox "Something went wrong!"
Err.Clear
End If
On Error GoTo 0
End Sub



(I used Jan, Feb, Mar.)

Remember that these will work in any workbook--until you turn them off.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[robertguy]

Dave,

many thanks indeed it worked a treat.....But is there a way to sa
input via a meeasge box and store the different dates assignd to eac
function key and update their releanvnt macro i.e. doDate1, doDate2
doDate3



Regards


Ro

 

[Dave Peterson]

I think I'd just use a couple of cells in a worksheet.

Put the description in column A and the value in column B.

I made a new worksheet called MyDates.
ColA ColB
Date1 08/28/2004
Date2 08/29/2004
Date3 08/30/2004

Then I used this version of the 3 "worker" macros:

Sub doDate1()
On Error Resume Next
Selection.Value = ThisWorkbook.Worksheets("mydates").Range("b1").Value
If Err.Number <> 0 Then
MsgBox "Something went wrong!"
Err.Clear
End If
On Error GoTo 0
End Sub
Sub doDate2()
On Error Resume Next
Selection.Value = ThisWorkbook.Worksheets("mydates").Range("b2").Value
If Err.Number <> 0 Then
MsgBox "Something went wrong!"
Err.Clear
End If
On Error GoTo 0
End Sub
Sub doDate3()
On Error Resume Next
Selection.Value = ThisWorkbook.Worksheets("mydates").Range("b3").Value
If Err.Number <> 0 Then
MsgBox "Something went wrong!"
Err.Clear
End If
On Error GoTo 0
End Sub

You could use an inputbox to retrieve these values, but I find that when I'm
editting in the worksheet, I make fewer mistakes--and I wouldn't have to worry
about adding code to validate input and to ask "are you sure you want this
value".