ipbr21054
Well-known Member
- Joined
- Nov 16, 2010
- Messages
- 5,226
- Office Version
- 2007
- Platform
- Windows
Morning,
I am using the code below which works fine apart from one thing i need some help with.
If ComboBox3 is HONDA then the year is entered into the cell at column I & the font is Red, this works fine.
If ComboBox3 is any other selection the year isnt entered into the cell at column I, this also works fine BUT
Once the userform values are transfered to worksheet the cell at I is empty so i would like a MsgBox to prompt the user to enter the year manually.
This MsgBox will then be shown after the user clicks OK to the MsgBox "SUCCESSFUL MESSAGE" shown in Red below
I am using the code below which works fine apart from one thing i need some help with.
If ComboBox3 is HONDA then the year is entered into the cell at column I & the font is Red, this works fine.
If ComboBox3 is any other selection the year isnt entered into the cell at column I, this also works fine BUT
Once the userform values are transfered to worksheet the cell at I is empty so i would like a MsgBox to prompt the user to enter the year manually.
This MsgBox will then be shown after the user clicks OK to the MsgBox "SUCCESSFUL MESSAGE" shown in Red below
Rich (BB code):
Private Sub CommandButton1_Click()
If OptionButton1.Value = True And OptionButton7.Value = False And OptionButton8.Value = False _
And OptionButton9.Value = False And OptionButton10.Value = False And OptionButton11.Value = False Then
MsgBox "You Must Select A Lead Type", vbCritical, "Lead Type Selection Error Message"
Else
If Len(Me.TextBox2.Value) = 17 Then
Dim i As Long, x As Long
Dim ControlsArr(1 To 8) As Variant, ns As Variant
Application.ScreenUpdating = False
For i = 1 To 8
ControlsArr(i) = Controls(IIf(i > 2, "ComboBox", "TextBox") & i).Value
Next i
With ThisWorkbook.Worksheets("MCLIST")
.Range("A8").EntireRow.Insert Shift:=xlDown
.Range("A8:K8").Borders.Weight = xlThin
.Cells(8, 1).Resize(, UBound(ControlsArr)).Value = ControlsArr
If ComboBox3.Value = "HONDA" Then
.Cells(8, 2).Characters(Start:=10, Length:=1).Font.Color = vbRed
.Cells(8, 9).Font.Color = vbRed
Else
.Cells(8, 2).Characters(Start:=10, Length:=1).Font.Color = vbBlack
.Cells(8, 9).Font.Color = vbBlack
End If
If OptionButton1.Value Then .Cells(8, 10).Value = "YES"
If OptionButton2.Value Then .Cells(8, 10).Value = "NO"
If OptionButton2.Value Then .Cells(8, 11).Value = "N/A"
If OptionButton7.Value Then .Cells(8, 11).Value = "BUNDLE"
If OptionButton8.Value Then .Cells(8, 11).Value = "GREY"
If OptionButton9.Value Then .Cells(8, 11).Value = "RED"
If OptionButton10.Value Then .Cells(8, 11).Value = "BLACK"
If OptionButton11.Value Then .Cells(8, 11).Value = "CLEAR"
ns = Array("X", "Y", "1", "2", "3", "4", "5", "6", "7", "8", "9", "A", "B", "C", _
"D", "E", "F", "G", "H", "J", "K", "L", "M", "N", "P", "R", "S")
For i = 0 To UBound(ns)
If Mid(Range("B8").Value, 10, 1) = ns(i) Then
Range("I8").Value = "" & 2000 + i
Exit For
End If
Next
Application.EnableEvents = False
If .AutoFilterMode Then .AutoFilterMode = False
x = .Cells(.Rows.Count, 1).End(xlUp).Row
.Range("A7:K" & x).Sort Key1:=.Range("A8"), Order1:=xlAscending, Header:=xlGuess
.Range("A:A").Find(TextBox1.Value, , xlValues, xlWhole).Select
Application.Goto Selection, True
End With
ActiveWorkbook.Save
MsgBox "Database Has Been Updated", vbInformation, "SUCCESSFUL MESSAGE"
Application.ScreenUpdating = True
Application.EnableEvents = True
Unload McListForm
Else
MsgBox "VIN MUST BE 17 CHARACTERS" & vbCr & vbCr & "DATABASE WAS NOT UPDATED", vbCritical, "MCLIST TRANSFER"
TextBox2.SetFocus
End If
End If
End Sub