CODEWITHSUNDEEP
exceldateformat
R3DSTON3R
R3DSTON3R

Reputation: 1

How to get excel VBA to format date

I am making a GUI in excel that allows the user to enter information into the spreadsheet just by using the GUI. I have run into a problem however. I can't seem to get the date to format right. this is the code I have for it (it being the code for the GUI:

Dim AN As Worksheet
Set AN = ThisWorkbook.Sheets("Sheet2")
Dim n As Long

n = AN.Range("A" & Application.Rows.Count).End(xlUp).Row

'AN.Range("A" & n + 1).Value ='
AN.Range("B" & n + 1).Value = Me.yyyy.Value + "-" + Me.mm.Value + "-" + Me.dd.Value
'AN.Range("C" & n + 1).Value ='
AN.Range("D" & n + 1).Value = Me.N_O_B.Value
AN.Range("E" & n + 1).Value = Me.Address_street.Value
AN.Range("F" & n + 1).Value = Me.City_info.Value
AN.Range("G" & n + 1).Value = Me.State_info.Value
AN.Range("H" & n + 1).Value = Me.Zip_info.Value
AN.Range("I" & n + 1).Value = Me.Lname.Value
AN.Range("J" & n + 1).Value = Me.Fname.Value
AN.Range("K" & n + 1).Value = Me.Minitial.Value
AN.Range("L" & n + 1).Value = Me.Suffix.Value
AN.Range("P" & n + 1).Value = Me.Busi_zip.Value
AN.Range("X" & n + 1).Value = Me.Shrt_Descript.Value

I am trying to get the date to show up in column B. It is showing up in column B, but not the way I thought it would. So in the GUI you enter the year, the month, and the date. Then I tried to make the code take the information and put it in a YYYY/MM/DD format but its not working. I have to have it in this format. Any advice would be helpful.

Upvotes: 0

Views: 63

Answers (1)

user4039065
user4039065

Reputation:

Force the date's raw value and then format the cell.

AN.Range("B" & n + 1).Value = dateserial(Me.yyyy.Value, Me.mm.Value, Me.dd.Value)
AN.Range("B" & n + 1).numberformat = "yyyy-mm-dd"

btw, the correct string concatenation operator is an ampersand (e.g. &). While a plus sign may work, it is best avoided, particularly when working with numbers.

Upvotes: 1

Related Questions