format Date in a Userform TextBox (yyyy-mm-dd) from data entered in a cell

[Blanchetdb]

Hi

I presently have the following:

Private Sub CommandButton1_Click()

Dim TargetRow As Integer
Dim FullName As String 'full name
Dim xRg As Range
Dim xCell As Range
Dim i As Long
Dim J As Long
Dim k As Long

Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 28).Value = TextBox24
Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 29).Value = TextBox2
Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 30).Value = TextBox25
Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 31).Value = TextBox21
Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 32).Value = TextBox26
Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 33).Value = TextBox9
Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 34).Value = TextBox27
Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 35).Value = TextBox22
Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 36).Value = TextBox4
Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 37).Value = TextBox3
Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 38).Value = TextBox23

there is more to the code but I inserted the pertinent information

the user enters a date in a cell using the following format "yyyy-mm-dd" but when activating the UserForm, the info appears (in the TextBox) in this format "mm/dd/yyyy"

how do I get the Date to appear in the UserForm TextBox in the following format yyyy-mm-dd

thank you

 

[Rick Rothstein]

Your code seems to be backward from what you are asking... your code is assigning a textbox value to a cell rather than the other way around. Assuming what you asked is in fact what you want, use the Format function. Something like this...

TextBox1.Value = Format(CellRef.Value, "yyyy-mm-dd")

Note: Replace my stand-in cell variable CellRef with the cell's actual reference.

 

[abdelfattah]

@Blanchetdb
for instance date in C2=yyyy-mm-dd
then should be like this

Textbox1.Text=range("C2").Value

and should correct your code based on your explanation.

 

[MARK858]

for instance date in C2=yyyy-mm-dd
then should be like this

Textbox1.Text=range("C2").Value

and should correct your code based on your explanation.

Doesn't for me

 

[abdelfattah]

thanks mark for test it , sorry about my error .
actually I don't test it .
should be

Textbox1.Value=range("C2").Text

 

[Blanchetdb]

Your code seems to be backward from what you are asking... your code is assigning a textbox value to a cell rather than the other way around. Assuming what you asked is in fact what you want, use the Format function. Something like this...

TextBox1.Value = Format(CellRef.Value, "yyyy-mm-dd")

Note: Replace my stand-in cell variable CellRef with the cell's actual reference.

Hi

you ae correct .... I inserted the wrong side of the code

this is where the data is extracted from the worksheet and the data populates the UserForm

TextAdvior.TextBox24 = Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 28).Value
TextAdvior.TextBox2 = Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 29).Value
TextAdvior.TextBox25 = Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 30).Value
TextAdvior.TextBox21 = Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 31).Value
TextAdvior.TextBox26 = Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 32).Value
TextAdvior.TextBox9 = Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 33).Value
TextAdvior.TextBox27 = Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 34).Value
TextAdvior.TextBox22 = Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 35).Value
TextAdvior.TextBox4 = Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 36).Value
TextAdvior.TextBox3 = Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 37).Value
TextAdvior.TextBox23 = Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 38).Value

so the value that is in the TargetRow and column 28, 29, 30,..... is in the Date format "yyyy-mm-dd" and I need the date to populate their respective TextBox in that same format.

Right now the cell has the correct format but it populates the TextBox in this format mm/dd/yyyy

thank you

 

[Alex Blakenburg]

You are quoting @Rick Rothstein's post that tells you how to do that ie

TextBox1.Value = Format(CellRef.Value, "yyyy-mm-dd")

It will be easier to read if you pull the Sheet and Range reference out using With

With Sheets("Staffing-Processes").Range("Ref")
    TextAdvior.TextBox24 = Format(.Offset(TargetRow, 28).Value, "yyyy-mm-dd")
    TextAdvior.TextBox2 = Format(.Offset(TargetRow, 29).Value, "yyyy-mm-dd")
    TextAdvior.TextBox25 = Format(.Offset(TargetRow, 30).Value, "yyyy-mm-dd")
    TextAdvior.TextBox21 = Format(.Offset(TargetRow, 31).Value, "yyyy-mm-dd")
    TextAdvior.TextBox26 = Format(.Offset(TargetRow, 32).Value, "yyyy-mm-dd")
    TextAdvior.TextBox9 = Format(.Offset(TargetRow, 33).Value, "yyyy-mm-dd")
    TextAdvior.TextBox27 = Format(.Offset(TargetRow, 34).Value, "yyyy-mm-dd")
    TextAdvior.TextBox22 = Format(.Offset(TargetRow, 35).Value, "yyyy-mm-dd")
    TextAdvior.TextBox4 = Format(.Offset(TargetRow, 36).Value, "yyyy-mm-dd")
    TextAdvior.TextBox3 = Format(.Offset(TargetRow, 37).Value, "yyyy-mm-dd")
    TextAdvior.TextBox23 = Format(.Offset(TargetRow, 38).Value, "yyyy-mm-dd")
End With

 

[Blanchetdb]

You are quoting @Rick Rothstein's post that tells you how to do that ie

TextBox1.Value = Format(CellRef.Value, "yyyy-mm-dd")

It will be easier to read if you pull the Sheet and Range reference out using With

With Sheets("Staffing-Processes").Range("Ref")
    TextAdvior.TextBox24 = Format(.Offset(TargetRow, 28).Value, "yyyy-mm-dd")
    TextAdvior.TextBox2 = Format(.Offset(TargetRow, 29).Value, "yyyy-mm-dd")
    TextAdvior.TextBox25 = Format(.Offset(TargetRow, 30).Value, "yyyy-mm-dd")
    TextAdvior.TextBox21 = Format(.Offset(TargetRow, 31).Value, "yyyy-mm-dd")
    TextAdvior.TextBox26 = Format(.Offset(TargetRow, 32).Value, "yyyy-mm-dd")
    TextAdvior.TextBox9 = Format(.Offset(TargetRow, 33).Value, "yyyy-mm-dd")
    TextAdvior.TextBox27 = Format(.Offset(TargetRow, 34).Value, "yyyy-mm-dd")
    TextAdvior.TextBox22 = Format(.Offset(TargetRow, 35).Value, "yyyy-mm-dd")
    TextAdvior.TextBox4 = Format(.Offset(TargetRow, 36).Value, "yyyy-mm-dd")
    TextAdvior.TextBox3 = Format(.Offset(TargetRow, 37).Value, "yyyy-mm-dd")
    TextAdvior.TextBox23 = Format(.Offset(TargetRow, 38).Value, "yyyy-mm-dd")
End With

thank you for the feedback but now I get the following

 

[Alex Blakenburg]

I have logged off for the night. That code worked for me.
You might need to share your workbook via dropbox, google drive or some other sharing platform and hopefully one of the others can sort it out for you.
If not I can have a look tomorrow.

 

[dmt32]

how do I get the Date to appear in the UserForm TextBox in the following format yyyy-mm-dd

Hi,
when returning values from a Range to a TextBox as already suggested, use the Range.Text property
This will return what you see in the cell (i.e. how you formatted the cell) rather than its underlying value.

Me.TextBox24.Value = Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 28).Text

If you still have issue then place copy of your workbook (with dummy data) on a file sharing site like dropbox & provide a link to it.

Dave