ipbr21054
Well-known Member
- Joined
- Nov 16, 2010
- Messages
- 5,738
- Office Version
- 2007
- Platform
- 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
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