Date in Userform to Worksheet..

[johnohio]

Hello All,
I have an excel userform that the data and is entered saved on a spreadsheet. I want the date to show as MM/DD/YY.
I have tried several suggestions for older posting but nothing seems to work.

I want cell B5 to show the date as MM/DD/YY once it is on the spreadsheet.
I cannot seem to get is to do that. Thanks!

Private Sub UserForm_Initialize()

Me.TextBox1 = Worksheets("a").Cells(6, 1) 'name
Me.TextBox2 = Worksheets("HOME").Cells(5, 7) 'begin bal
Me.TextBox3 = Worksheets("HOME").Cells(5, 2)  'begin date

Me.TextBox2 = Format(Me.TextBox2.Value, "$#,##0.00")
Me.TextBox3 = Format(Me.TextBox3.Value, "mm/dd/yy")

End Sub

Private Sub CommandButton1_Click()

If Me.TextBox1 = "" Or Me.TextBox2 = "" Or Me.TextBox3 = "" Then
    MsgBox ("Please complete all fields")
    Exit Sub
    End If
 
If IsNumeric(Me.TextBox2) = False Then
    MsgBox ("Opening Balance must be numeric")
    Exit Sub
    End If
    
If IsDate(Me.TextBox3) = False Then
    MsgBox ("Date is not valid")
    Exit Sub
    End If


x = MsgBox("Are you sure you want to save this change?", vbQuestion + vbYesNo, "STOP")
If x = vbNo Then
    Exit Sub
    End If
  
 
Worksheets("a").Cells(1, 4) = Me.TextBox1 'name at top
Worksheets("HOME").Range("b5").NumberFormat = "mm/dd/yy" '<<<<<<<< CAN'T GET TO WORK <<<<<<<<
Worksheets("HOME").Cells(5, 2) = Me.TextBox3


Worksheets("HOME").Cells(5, 7) = 1 * Me.TextBox2
Worksheets("HOME").Cells(5, 3) = "Opening Balance"
Worksheets("HOME").Cells(5, 4) = ". . . . ."


Worksheets("HOME").Cells(5, 11) = 1 * Me.TextBox2
Worksheets("HOME").Cells(5, 12) = 1 * Me.TextBox2
Worksheets("HOME").Cells(5, 7) = Me.TextBox2
    
Unload Me

End Sub

 

[DanteAmor]

Replace this lines:

Worksheets("HOME").Range("b5").NumberFormat = "mm/dd/yy" '<<<<<<<< CAN'T GET TO WORK <<<<<<<<
Worksheets("HOME").Cells(5, 2) = Me.TextBox3

For this:

Worksheets("HOME").Range("B5").Value = CDate(Me.TextBox3.Value)
Worksheets("HOME").Range("B5").NumberFormat = "mm/dd/yy"

In the textbox the data is stored as text, in this case, you must convert that text to date, with the CDate function.


--------------
Note: To optimize your code:
You can reference the sheet using the With statement
That way you only write the name of the sheet once. Notice how each line that will refer to the sheet is preceded by a dot.

Private Sub CommandButton1_Click()
  If Me.TextBox1 = "" Or Me.TextBox2 = "" Or Me.TextBox3 = "" Then
    MsgBox ("Please complete all fields")
    Exit Sub
  End If
  If IsNumeric(Me.TextBox2) = False Then
    MsgBox ("Opening Balance must be numeric")
    Exit Sub
  End If
  If IsDate(Me.TextBox3) = False Then
    MsgBox ("Date is not valid")
    Exit Sub
  End If
 
  x = MsgBox("Are you sure you want to save this change?", vbQuestion + vbYesNo, "STOP")
  If x = vbNo Then
  Exit Sub
  End If
 
  Worksheets("a").Cells(1, 4) = Me.TextBox1 'name at top
 
  With Worksheets("HOME")
    .Range("B5").Value = CDate(Me.TextBox3.Value)
    .Range("B5").NumberFormat = "mm/dd/yy" '<<<<<<<< CAN'T GET TO WORK <<<<<<<<
 
    .Cells(5, 7) = 1 * Me.TextBox2
    .Cells(5, 3) = "Opening Balance"
    .Cells(5, 4) = ". . . . ."
 
    .Cells(5, 11) = 1 * Me.TextBox2
    .Cells(5, 12) = 1 * Me.TextBox2
    .Cells(5, 7) = Me.TextBox2
 
    Unload Me
  End With
End Sub

 

[johnohio]

Replace this lines:

Worksheets("HOME").Range("b5").NumberFormat = "mm/dd/yy" '<<<<<<<< CAN'T GET TO WORK <<<<<<<<
Worksheets("HOME").Cells(5, 2) = Me.TextBox3

For this:

Worksheets("HOME").Range("B5").Value = CDate(Me.TextBox3.Value)
Worksheets("HOME").Range("B5").NumberFormat = "mm/dd/yy"

In the textbox the data is stored as text, in this case, you must convert that text to date, with the CDate function.


--------------
Note: To optimize your code:
You can reference the sheet using the With statement
That way you only write the name of the sheet once. Notice how each line that will refer to the sheet is preceded by a dot.

Private Sub CommandButton1_Click()
  If Me.TextBox1 = "" Or Me.TextBox2 = "" Or Me.TextBox3 = "" Then
    MsgBox ("Please complete all fields")
    Exit Sub
  End If
  If IsNumeric(Me.TextBox2) = False Then
    MsgBox ("Opening Balance must be numeric")
    Exit Sub
  End If
  If IsDate(Me.TextBox3) = False Then
    MsgBox ("Date is not valid")
    Exit Sub
  End If
 
  x = MsgBox("Are you sure you want to save this change?", vbQuestion + vbYesNo, "STOP")
  If x = vbNo Then
  Exit Sub
  End If
 
  Worksheets("a").Cells(1, 4) = Me.TextBox1 'name at top
 
  With Worksheets("HOME")
    .Range("B5").Value = CDate(Me.TextBox3.Value)
    .Range("B5").NumberFormat = "mm/dd/yy" '<<<<<<<< CAN'T GET TO WORK <<<<<<<<
 
    .Cells(5, 7) = 1 * Me.TextBox2
    .Cells(5, 3) = "Opening Balance"
    .Cells(5, 4) = ". . . . ."
 
    .Cells(5, 11) = 1 * Me.TextBox2
    .Cells(5, 12) = 1 * Me.TextBox2
    .Cells(5, 7) = Me.TextBox2
 
    Unload Me
  End With
End Sub

PERFECT!
And thanks for the coding tip!