VBA project using SendKeys

[SunOffice]

Hi All,

Can you please help me on using sendkeys for 'DATE' and 'TIME' function?

I manually use for Date —› Ctrl + ;
I manually use for Time —› Ctrl + Shift + ;

If I use formulas - "=Today()" and "=Now()" in my VBA codes then I have to use copy & paste codes, so that the date & time get fixed, and don't change/ update if I use codes for the next time.

Thanks in Advance!

[Colin Legg]

Hi

If I use formulas - "=Today()" and "=Now()" in my VBA codes then I have to use copy & paste codes, so that the date & time get fixed, and don't change/ update if I use codes for the next time.

If you want to put the date/time into a cell, why don't you just use the VBA functions?

eg.

Range("A1").Value = VBA.Date

Trust me, Sendkeys can be quite unreliable and should be avoided when possible.

[SunOffice]

Sub AutoDateTime()
'
'
' Keyboard Shortcut: Ctrl+w
'
    'Selection.End(xlDown).Select
    Range("C1048576").Select
    Selection.End(xlUp).Offset(1, 0).Select
        
    If ActiveCell.Offset(-1, 0).Value <> "" And ActiveCell.Offset(0, 0).Value = "" And ActiveCell.Offset(0, -1) = "" Then
    ActiveCell.Offset(0, 0).Select
    

    ActiveCell.Value = VBA.Time                       ' for the current time of the system
    ActiveCell.Offset(0, -2).Value = VBA.Date       ' for the current date of the system
    

    Else
    End If
    ActiveCell.Offset(1, 0).Select

End Sub

Thanks Colin Legg for ur amazing help on this.
...and okay! I will try to avoid using the send-keys in my codes.

[SunOffice]

Is there any VBA code for entering Weekday's Names into the next cell like Monday, Tuesday etc.?
for example below codes for the current time & date.

    ActiveCell.Value = VBA.Time                       ' for the current time of the system
    ActiveCell.Offset(0, -2).Value = VBA.Date       ' for the current date of the system

[Colin Legg]

Hi,

Yes, you can use the Format function to do that, for example:

ActiveCell.Value = Format(Date, "dddd")

[SunOffice]

Thanks a world for ur amazing help!

    ActiveCell.Value = VBA.Time                       ' for the current time of the system
    ActiveCell.Offset(0, -2).Value = VBA.Date           ' for the current date of the system
    ActiveCell.Offset(0, 1).Value = Format(Date, "dddd")    ' for the weekday's name as per the current date
    ActiveCell.Offset(0, 2).Value = Format(Date, "mmmm")    ' for the month's name as per the current date
    ActiveCell.Offset(0, 3).Value = Format(Date, "yyyy")    ' for the year's number as per the current date
                                                                   ' We can change the format by increasing/ decreasing the values in " " above formulas.

P.S. - I'd like to learn these smart tricks.