CODEWITHSUNDEEP
excelvba
RkVeer
RkVeer

Reputation: 1

Date format changes when userform text box sends a required date format to sheet

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:

enter image description here

The same dates are showing differently in the sheet:

enter image description here

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

Answers (1)

Eli Kaplow
Eli Kaplow

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

Related Questions