Format date in userform textbox

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,199
Office Version
  1. 2007
Platform
  1. Windows
Morning,

I am using the code shown below.

My problem is when i open up the userform TextBox2 should show todays day,but nothing is shown.

Once i then press the command button to transfer the userform data to my worksheet the date then appears.
Can you advise where / how i have the date shown when i open the userform.

I have now confused myself trying to sort this & as a result im lost.

many thanks


Code:
Private Sub CommandButton1_Click()

    With Sheets("DATABASE")
    
    If Not Application.CountIf(.Columns(16), Me.ComboBox13.Text) > 0 Or Me.ComboBox13.Text = "N/A" Then


     Rows("6:6").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
     Range("A6").Select
     Range("A6:Q6").Borders.LineStyle = xlContinuous
     Range("A6:Q6").Borders.Weight = xlThin
     Range("A6:Q6").Interior.ColorIndex = 6
     Range("M6") = Date
     Range("$Q$6").Value = "'NO NOTES FOR THIS CUSTOMER"
     Range("$Q$6").HorizontalAlignment = xlCenter
     
    .Range("A6").Value = Me.TextBox1.Text
    .Range("B6").Value = Me.ComboBox1.Text
    .Range("C6").Value = Me.ComboBox2.Text
    .Range("D6").Value = Me.ComboBox3.Text
    .Range("E6").Value = Me.ComboBox4.Text
    .Range("F6").Value = Me.ComboBox5.Text
    .Range("G6").Value = Me.ComboBox6.Text
    .Range("H6").Value = Me.ComboBox7.Text
    .Range("I6").Value = Me.ComboBox8.Text
    .Range("J6").Value = Me.ComboBox9.Text
    .Range("K6").Value = Me.ComboBox10.Text
    .Range("L6").Value = Me.ComboBox11.Text
    .Range("M6").Value = Me.TextBox2.Text
    .Range("N6").Value = Me.ComboBox12.Text
    .Range("O6").Value = Me.TextBox3.Text
    .Range("P6").Value = Me.ComboBox13.Text
    .Range("Q6").Value = Me.TextBox5.Text
    
            ElseIf Len(Me.ComboBox13.Text) = 0 Then
            With Me.ComboBox13
                 MsgBox "You Must Enter An Invoice Number", vbCritical, "INVOICE NUMBER FIELD EMPTY MESSAGE"
                .Value = "": .SetFocus
            End With
            Exit Sub
        Else
            With Me.ComboBox13
                MsgBox "Invoice Number " & .Text & " Already Exists", vbCritical, "DUPLICATE INVOICE NUMBER MESSAGE"
                .Value = "": .SetFocus
            End With
            Exit Sub
        End If
    End With


Dim ctrl As MSForms.Control
 
For Each ctrl In Me.Controls
    Select Case True
                Case TypeOf ctrl Is MSForms.TextBox
            ctrl.Value = ""
        Case TypeOf ctrl Is MSForms.combobox
            ctrl.Value = ""
    End Select
    
Next ctrl
    MsgBox "Database Has Been Updated", vbInformation, "SUCCESSFUL TRANSFER MESSAGE"
    
    TextBox2.Value = Now
    TextBox2 = Format(TextBox2.Value, "dd/mm/yyyy")
    TextBox1.SetFocus
    TextBox5.Value = "NO NOTES FOR THIS CUSTOMER"


End Sub
Private Sub CommandButton2_Click()
Unload DatabaseInput
Range("A6").Select
End Sub
Private Sub TextBox1_Change()
    TextBox1 = UCase(TextBox1)
End Sub
Private Sub TextBox2_Change()
    TextBox2 = UCase(TextBox2)
    TextBox2 = Format(TextBox2.Value, "dd/mm/yyyy")
End Sub
Private Sub TextBox3_Change()
    TextBox3 = UCase(TextBox3)
End Sub
Private Sub TextBox4_Change()
    TextBox4 = UCase(TextBox4)
End Sub


Private Sub UserForm_Initialize()
'REGISTRATION NUMBER
Dim lastrowr As Long
lastrowr = Sheets("INFO").Cells(Rows.Count, "R").End(xlUp).Row
ComboBox1.List = Sheets("INFO").Cells(2, "R").Resize(lastrowr).Value


'BLANK USED
Dim lastrowl As Long
lastrowl = Sheets("INFO").Cells(Rows.Count, "L").End(xlUp).Row
ComboBox2.List = Sheets("INFO").Cells(2, "L").Resize(lastrowl).Value


'VEHICLE
Dim lastrowb As Long
lastrowb = Sheets("INFO").Cells(Rows.Count, "B").End(xlUp).Row
ComboBox3.List = Sheets("INFO").Cells(2, "B").Resize(lastrowb).Value


'BUTTONS
Dim lastrowj As Long
lastrowj = Sheets("INFO").Cells(Rows.Count, "J").End(xlUp).Row
ComboBox4.List = Sheets("INFO").Cells(2, "J").Resize(lastrowj).Value


'ITEM SUPPLIED
Dim lastrowt As Long
lastrowt = Sheets("INFO").Cells(Rows.Count, "T").End(xlUp).Row
ComboBox5.List = Sheets("INFO").Cells(2, "T").Resize(lastrowt).Value


'TRANSPONDER CHIP
Dim lastrowf As Long
lastrowf = Sheets("INFO").Cells(Rows.Count, "F").End(xlUp).Row
ComboBox6.List = Sheets("INFO").Cells(2, "F").Resize(lastrowf).Value


'JOB ACTION
Dim lastrowh As Long
lastrowh = Sheets("INFO").Cells(Rows.Count, "H").End(xlUp).Row
ComboBox7.List = Sheets("INFO").Cells(2, "H").Resize(lastrowh).Value


'PROGRAMMER USED
Dim lastrowd As Long
lastrowd = Sheets("INFO").Cells(Rows.Count, "D").End(xlUp).Row
ComboBox8.List = Sheets("INFO").Cells(2, "D").Resize(lastrowd).Value


'KEY CODE
Dim lastrowp As Long
lastrowp = Sheets("INFO").Cells(Rows.Count, "P").End(xlUp).Row
ComboBox9.List = Sheets("INFO").Cells(2, "P").Resize(lastrowp).Value


'BITING
Dim lastrowx As Long
lastrowx = Sheets("INFO").Cells(Rows.Count, "X").End(xlUp).Row
ComboBox10.List = Sheets("INFO").Cells(2, "X").Resize(lastrowx).Value


'CHASSIS NUMBER
Dim lastrown As Long
lastrown = Sheets("INFO").Cells(Rows.Count, "N").End(xlUp).Row
ComboBox11.List = Sheets("INFO").Cells(2, "N").Resize(lastrown).Value


'VEHCILE YEAR
Dim lastrowv As Long
lastrowv = Sheets("INFO").Cells(Rows.Count, "V").End(xlUp).Row
ComboBox12.List = Sheets("INFO").Cells(2, "V").Resize(lastrowv).Value


'INVOICE NUMBER
Dim lastroww As Long
lastroww = Sheets("INFO").Cells(Rows.Count, "W").End(xlUp).Row
ComboBox13.List = Sheets("INFO").Cells(2, "W").Resize(lastroww).Value


End Sub
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
There is no code in the Userform_Initialize event to populate any textbox, which is why you aren't seeing anything.
 
Upvote 0
Thanks,
So now adding this is correct

Code:
TextBox2.Value = Format(CDbl(Date), "dd/mm/yyyy")
 
Upvote 0
The CDbl is unnecessary there.
 
Upvote 0
So is this correct.
TextBox2.Value = Format(Date), "dd/mm/yyyy")
 
Upvote 0
Hi,

This is correct.
TextBox2.Value = Format(Date, "dd/mm/yyyy") :))
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top