Reputation: 1
I have a userform from where I send a date in mm/yy (Month/Year) format to the worksheet. When transferred to the sheet, it changes format and in another location of the sheet it is displaying very different.
For date format, I'm using the following code:
TextBox3.SetFocus
If IsDate(TextBox3.Text) Then
Debug.Print Format(CDate(TextBox3.Text), "mm/yy")
Else
Debug.Print "Not a valid date"
End If
Transferring Code:
Dim X As Long
Dim Y As Worksheet
Set Y = Sheets2
X = Y.Range("B" & Rows.Count).End(xlUp).Row
with Y
.Cells(X + 1, "D").Value = TextBox3.Value
End with
When I transfer date from userform it shows like this:
The same dates are showing differently in the sheet:
I want to display the date in the format Month and Year. For example, if a date is June-2021 then show as 06/21.
Upvotes: 0
Views: 722
Reputation: 36
It's hard to tell where you need help since the code you provided just prints the date in the immediate window. In general, you need to make formatting changes to the cell itself. So after you set the data from the form, do something like this to your worksheet.
Range("A2:A50").NumberFormat = "mm/yy"
Edit: To update the code above
Dim X As Long
Dim Y As Worksheet
Set Y = Sheets2
X = Y.Range("B" & Rows.Count).End(xlUp).Row
with Y
.Cells(X + 1, "D").Value = TextBox3.Value
.Cells(X + 1, "D").NumberFormat = "mm/yy"
End with
Upvotes: 1