Press one key to populate the date

[BBoyAl]

Good Evening,

Does anyone know if there is a way where I could format a cell range so that if I were to type just one specific letter/key into a cell within the range it would populate the current date.


Appreciate any help and ideas.

Thanks.

[Paul]

You would only be able to do so using a Worksheet_Change event macro, and it would be two keys - the letter and then Enter.

Please Login or Register  to view this content.

You could instead use the Worksheet_BeforeDoubleClick event and check to see which column the double-click occurred in, and insert a date whenever you double-click in that column.

Please Login or Register  to view this content.

[BBoyAl]

Thanks Paul,

That looks sounds good. Unfortunately I am not very experienced with excel and am really not sure where to put that code or what to do with it. lol.

I am trying to get it to work for AC5:AG32 Sheet2. If it is too complex perhaps I should just stick with typing the date in.

The workbook is far from finished but feel free to take a look if you like.


Any help and advice is very much appreciated.

[Paul]

To get either code option to work, just right-click on the Sheet2 tab in the workbook and choose View Code. Paste one of the code snippets I provided above into the VB Editor window that appeared. Adjust the Range("C:C") bit to Range("AC5:AG32"). Close the VB Editor.

I recommend the double-click event, as it's easier than typing into a cell if you use the mouse a lot. If you're a keyboard aficionado, use the Worksheet_Change event instead.

[BBoyAl]

Paul that code works perfectly!

I opted for the double click one. I want the cell to highlight in an Amber colour if it has a date in. The date format is dd/mm, however I can't seem to find a function to do this. I think it might need a formula, I have tried to write a few but can get one work. If you have an idea please let me know.

Have a great day.

[Paul]

Use Conditional Formatting. See here: http://chandoo.org/wp/2010/01/05/con...matting-dates/

You can also search for "conditional format dates"

[BBoyAl]

Nice one. Thanks