We help IT Professionals succeed at work.

Have today date appear automatically on the a column when using this macro

Omar Hernandez
on
How can i get the date to appear on the first column of the row when using this macro.

'copy the data to the database
        ws.Cells(iRow, 1).Value =
        ws.Cells(iRow, 3).Value = "          " & Me.textbox_name.Value
        ws.Cells(iRow, 4).Value = Me.Label4.Caption
        ws.Cells(iRow, 5).Value = Me.Label3.Caption
        ws.Cells(iRow, 6).Value = Me.Label5.Caption
        ws.Cells(iRow, 7).Value = "2"
Comment
Watch Question

Roy CoxGroup Finance Manager

Commented:
ws.Cells(iRow, 1).Value =Date

Open in new window

Author

Commented:
Hey Roy Cox, it works wanted to know if there is a way to change the way it looks (9/24/2016) to this (9/24/16)
Roy CoxGroup Finance Manager

Commented:
Change the Format

ws.Cells(iRow, 1).Value =Format(Date,"mm/dd/yy")

Open in new window

Author

Commented:
It keeps showing the format as "mm/dd/yyyy"
Group Finance Manager
Commented:
Try

ws.Cells(iRow, 1).Value =Format(Date,"mm/dd/yy")
ws.Cells(iRow, 1).NumberFormat="mm/dd/yy")

Open in new window


Are the cells pre-formatted to "mm/dd/yyyy"
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Maybe this?
ws.Cells(iRow, 1).NumberFormat = "m/d/yy"
ws.Cells(iRow, 1).Value = Format(Date, "m/d/yy")

Open in new window

Author

Commented:
Thank's again Neeraj the last suggestion that you have provided, works phenomenal. I have a pop up calendar function that when i click on cells it automatically pops up.

In order for that the pop up calendar to appear i would need to
1. Right click on the cell where i want the calendar to pop up.
2. On the number column of the box i need to click then on date.
3. Type of date 3/14/12

***Any way i can implement so that it can record this function on the macro.
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're question is not clear to me.
As you said, you already have a code to input a date through the calendar pop up in a cell and I assume that works for you. So do you want to apply the same number format to that date cell or what?

It would be helpful if you can upload a sample workbook so that it would be easy to visualize your requirement more clearly.

Author

Commented:
Thanks Roy Cox this worked great
Roy CoxGroup Finance Manager

Commented:
Pleased to help.
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
:)
Roy CoxGroup Finance Manager

Commented:
Having seen the mention of your calendar then the formatting should be done within the calendar form.

See my example workbook
Calendar-Forms.xlsm