ExcelBanter - View Single Post

Wrote:
I need help creating a macro that lets you type in 7p that will show up
as 7:00 PM. Is this possible?

Thanks
Lori
Create a macro with this line:
Application.OnKey "{PGDN}", "cop"
You can use any key in place of PGDN that you probably won't be using.
Press F1 while the cursor is on OnKey and you'll get a list of possible
keys.

Create another macro:
Sub cop()
d = ActiveCell.Offset(0, 1).Value
ActiveCell.Value = d
End Sub

In the cell to the right of the cell you will enter your time place F3
in the example this formula:
=LEFT(E3,LEN(E3)-1) & ":00" & IF(RIGHT(E3,1)="p","PM","AM")

Start the first macro. It is continuously looking for the Page Down
key to be used and when it is it runs the cop procedure.
When you key in a time, e.g. 7p, and press Page Down 7:00PM will
replace the 7p.

I hope this answers your question.
Bob

Note: You can hide the cells with the formula and protect them for
appearance and security sake.

--
jahoobob