Macro for current date

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:45 PM GMT

Question

I need a Macro which gives me Current date in mm/dd/yyyy format in Active cell if the active cell is in a specific column. for example if I select cell "C12" as active cell %26 run the macro it should give me today's date in cell "C12" but if Selected cell "A10" or cell "B15" the Macro does not give any output.

I need the macro for date %26 time Separately but I hope if I get the code for one I will be able to do the other one myself.

It will be great if you can help me with this.

Thanks %26 Mainak Mukherjee

Answer

Mainak Mukherjee,

on the sheet where you want this behavior, right click on the sheet tab and select view code.

In the resulting module, you want to use the selectionchange event. will sometimes put this in the module by default:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

if it is there use that. If not, in the dropdowns at the top of the module in the left dropdown select "Worksheet". In the right dropdown select "SelectionChange" (not "Change") and it will put in that declaraton:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub


add this code


Private Sub Worksheet_SelectionChange(ByVal Target As Range) if Target.count > 1 then exit sub if Target.column <> 3 then exit sub On Error goto ErrHandler Application.EnableEvents = False Target.value = Date Target.Numberformat = "mm/dd/yyyy" ' change to suit ErrHandler: Application.EnableEvents = True End Sub

That worked for me as I understand your requirement.

Advertisement

©2021 eLuminary LLC. All rights reserved.