Dim wsIngMaster As Worksheet, wsIngData As Worksheet
Private Sub UserForm_Initialize()
Dim LastRow As Long
Set wsIngMaster = ThisWorkbook.Worksheets("IngMaster")
Set wsIngData = ThisWorkbook.Worksheets("IngData")
Me.DTPicker1.Value = Date
LastRow = wsIngMaster.Range("A" & wsIngMaster.Rows.count).End(xlUp).Row
Me.ComboBox1.List = wsIngMaster.Cells(3, 1).Resize(LastRow, 1).Value2
Me.CommandButton1.Enabled = False
With Application
.WindowState = xlMaximized
Me.Zoom = Int(.width / Me.width * 80)
Me.width = .width
Me.height = .height
End With
End Sub
Private Sub ComboBox1_Change()
With Me.ComboBox1
Me.TextBox1.Value = wsIngMaster.Cells(.ListIndex + 3, 2).Value
Me.CommandButton1.Enabled = CBool(Len(.Text) > 0)
TextBox2.SetFocus
End With
End Sub
Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Call matchText
End Sub
Sub matchText()
If ComboBox1.Value = "" Or TextBox3.Value = "" Then Exit Sub
If TextBox3.Value = ComboBox1.Value Then
TextBox1.BackColor = RGB(51, 255, 51)
TextBox2.BackColor = RGB(51, 255, 51)
TextBox3.BackColor = RGB(51, 255, 51)
ComboBox1.BackColor = RGB(51, 255, 51)
Else
TextBox1.BackColor = RGB(255, 0, 0)
TextBox2.BackColor = RGB(255, 0, 0)
TextBox3.BackColor = RGB(255, 0, 0)
ComboBox1.BackColor = RGB(255, 0, 0)
result = MsgBox("NO MATCH Please Try Again", vbOKOnly)
If result = vbOK Then
Dim LastRow As Long, ws As Worksheet
Set ws = Sheets("IngData")
LastRow = ws.Range("A" & Rows.count).End(xlUp).Row + 1 'Finds the last blank row
ws.Range("B" & LastRow).Value = TextBox1.Text
ws.Range("I" & LastRow).Value = TextBox2.Text
ws.Range("C" & LastRow).Value = TextBox3.Text
ws.Range("E" & LastRow).Value = TextBox4.Text
ws.Range("A" & LastRow).Value = ComboBox1.Text
ws.Range("J" & LastRow).Value = opt_In.Value
Columns("J").Select
Selection.Replace What:="True", Replacement:="FAIL", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
CheckBox1.Value = False
Range("K" & Rows.count).End(xlUp).Offset(1).Value = Now
ComboBox1.Value = ""
TextBox1.Value = ""
TextBox2.Value = ""
TextBox3.Value = ""
TextBox1.BackColor = RGB(255, 255, 255)
TextBox2.BackColor = RGB(255, 255, 255)
TextBox3.BackColor = RGB(255, 255, 255)
ComboBox1.BackColor = RGB(255, 255, 255)
End If
End If
End Sub
Private Sub CommandButton1_Click()
Dim NewRow As Long
Dim m As Variant
Application.DisplayAlerts = False
With wsIngData
NewRow = .Range("A" & .Rows.count).End(xlUp).Row + 1
.Range("B" & NewRow).Value = Me.TextBox1.Text
.Range("I" & NewRow).Value = Me.TextBox2.Text
.Range("C" & NewRow).Value = Me.TextBox3.Text
.Range("D" & NewRow).Value = Me.TextBox4.Text
.Range("E" & NewRow).Value = Me.TextBox5.Text
.Range("F" & NewRow).Value = Me.TextBox6.Text
.Range("H" & NewRow).Value = Me.TextBox7.Text
.Range("A" & NewRow).Value = Me.ComboBox1.Text
.Range("J" & NewRow).Value = Me.opt_In.Value
.Range("G" & NewRow).Value = Me.DTPicker1.Value
.Range("K" & NewRow).Value = Now
.Columns("J").Replace What:="True", Replacement:="IN", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End With
'update master
With wsIngMaster
m = Application.Match(Val(Me.ComboBox1.Text), .Columns(1), 0)
If Not IsError(m) Then
With .Cells(CLng(m), "D")
.Value = .Value + Val(Me.TextBox5.Value)
End With
End If
End With
opt_In.Value = False
Application.DisplayAlerts = True
Unload Me
End Sub
Private Sub commandbutton2_click()
Unload Me
End Sub