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
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