When I try to test this code, I'm getting an Error Type 13, but I haven't been able to find where there's a data mismatch. Thoughts?
Code:
Option Explicit
Dim coboDict As Object
Private Sub cmd_Close_Click()
Unload Me
End Sub
Private Sub cmd_Submit_Click()
Dim ws1 As Worksheet
Dim LastRow As Long
Set ws1 = ThisWorkbook.Sheets("Info")
LastRow = ws1.Range("G" & Rows.Count).End(xlUp).Row + 1
ws1.Range("A" & LastRow).Value = "=Today()"
ws1.Range("B" & LastRow).Value = CDate(Me.txt_Updated)
ws1.Range("C" & LastRow).Value = (Me.cobo_Status)
ws1.Range("D" & LastRow).Value = (Me.txt_First)
ws1.Range("E" & LastRow).Value = (Me.txt_Last)
ws1.Range("F" & LastRow).Value = (Me.txt_Suff)
ws1.Range("G" & LastRow).Value = (Me.cobo_Name)
ws1.Range("H" & LastRow).Value = CDate(Me.txt_DoB)
ws1.Range("I" & LastRow).Value = (Me.cobo_Gender)
ws1.Range("J" & LastRow).Value = (Me.txt_SignupAge)
ws1.Range("L" & LastRow).Value = (Me.txt_Phone)
ws1.Range("M" & LastRow).Value = (Me.txt_Email)
If Not Len(Me.txt_DPStart) = 0 Then ws1.Range("O" & LastRow).Value = CDate(Me.txt_DPStart)
If Not Len(Me.txt_DPEnd) = 0 Then ws1.Range("P" & LastRow).Value = CDate(Me.txt_DPEnd)
If Not Len(Me.txt_DPAmt) = 0 Then ws1.Range("Q" & LastRow).Value = CCur(Me.txt_DPAmt)
If Not Len(Me.cobo_DPFreq) = 0 Then ws1.Range("R" & LastRow).Value = (Me.cobo_DPFreq)
If Not Len(Me.txt_DCStart) = 0 Then ws1.Range("T" & LastRow).Value = CDate(Me.txt_DCStart)
If Not Len(Me.txt_DCEnd) = 0 Then ws1.Range("U" & LastRow).Value = CDate(Me.txt_DCEnd)
If Not Len(Me.txt_DCAmt) = 0 Then ws1.Range("V" & LastRow).Value = CCur(Me.txt_DCAmt)
If Not Len(Me.cobo_DCFreq) = 0 Then ws1.Range("W" & LastRow).Value = (Me.cobo_DCFreq)
If Not Len(Me.txt_OCStart) = 0 Then ws1.Range("Y" & LastRow).Value = CDate(Me.txt_OCStart)
If Not Len(Me.txt_OCEnd) = 0 Then ws1.Range("Z" & LastRow).Value = CDate(Me.txt_OCEnd)
If Not Len(Me.txt_OCAmt) = 0 Then ws1.Range("AA" & LastRow).Value = CCur(Me.txt_OCAmt)
If Not Len(Me.cobo_OCFreq) = 0 Then ws1.Range("AB" & LastRow).Value = (Me.cobo_OCFreq)
If Not Len(Me.txt_CTIStart) = 0 Then ws1.Range("AD" & LastRow).Value = CDate(Me.txt_CTIStart)
If Not Len(Me.txt_CTIEnd) = 0 Then ws1.Range("AE" & LastRow).Value = CDate(Me.txt_CTIEnd)
If Not Len(Me.txt_CTIAmt) = 0 Then ws1.Range("AF" & LastRow).Value = CCur(Me.txt_CTIAmt)
If Not Len(Me.cobo_CTIFreq) = 0 Then ws1.Range("AG" & LastRow).Value = (Me.cobo_CTIFreq)
If Not Len(Me.txt_CTOStart) = 0 Then ws1.Range("AI" & LastRow).Value = CDate(Me.txt_CTOStart)
If Not Len(Me.txt_CTOEnd) = 0 Then ws1.Range("AJ" & LastRow).Value = CDate(Me.txt_CTOEnd)
If Not Len(Me.txt_CTOAmt) = 0 Then ws1.Range("AK" & LastRow).Value = CCur(Me.txt_CTOAmt)
If Not Len(Me.cobo_CTOFreq) = 0 Then ws1.Range("AL" & LastRow).Value = (Me.cobo_CTOFreq)
ws1.Range("N" & LastRow).Value = "=IF(RC[1]="""",""Inactive"",IF(RC[2]<>"""",""Inactive"",""Active""))"
ws1.Range("S" & LastRow).Value = "=IF(RC[1]="""",""Inactive"",IF(RC[2]<>"""",""Inactive"",""Active""))"
ws1.Range("X" & LastRow).Value = "=IF(RC[1]="""",""Inactive"",IF(RC[2]<>"""",""Inactive"",""Active""))"
ws1.Range("AC" & LastRow).Value = "=IF(RC[1]="""",""Inactive"",IF(RC[2]<>"""",""Inactive"",""Active""))"
ws1.Range("AH" & LastRow).Value = "=IF(RC[1]="""",""Inactive"",IF(RC[2]<>"""",""Inactive"",""Active""))"
End Sub
Private Sub cobo_Name_Change()
With Sheets("Info")
Me.txt_Updated = .Cells(coboDict.Item(Me.cobo_Name.Value), "B").Value
Me.cobo_Status = .Cells(coboDict.Item(Me.cobo_Name.Value), "C").Value
Me.txt_First = .Cells(coboDict.Item(Me.cobo_Name.Value), "D").Value
Me.txt_Last = .Cells(coboDict.Item(Me.cobo_Name.Value), "E").Value
Me.txt_Suff = .Cells(coboDict.Item(Me.cobo_Name.Value), "F").Value
Me.txt_DoB = .Cells(coboDict.Item(Me.cobo_Name.Value), "H").Value
Me.cobo_Gender = .Cells(coboDict.Item(Me.cobo_Name.Value), "I").Value
Me.txt_SignupAge = .Cells(coboDict.Item(Me.cobo_Name.Value), "J").Value
Me.txt_Phone = .Cells(coboDict.Item(Me.cobo_Name.Value), "L").Value
Me.txt_Email = .Cells(coboDict.Item(Me.cobo_Name.Value), "M").Value
Me.cobo_DPStatus = .Cells(coboDict.Item(Me.cobo_Name.Value), "N").Value
Me.txt_DPStart = .Cells(coboDict.Item(Me.cobo_Name.Value), "O").Value
Me.txt_DPEnd = .Cells(coboDict.Item(Me.cobo_Name.Value), "P").Value
Me.txt_DPAmt = .Cells(coboDict.Item(Me.cobo_Name.Value), "Q").Value
Me.cobo_DPFreq = .Cells(coboDict.Item(Me.cobo_Name.Value), "R").Value
Me.cobo_DCStatus = .Cells(coboDict.Item(Me.cobo_Name.Value), "S").Value
Me.txt_DCStart = .Cells(coboDict.Item(Me.cobo_Name.Value), "T").Value
Me.txt_DCEnd = .Cells(coboDict.Item(Me.cobo_Name.Value), "U").Value
Me.txt_DCAmt = .Cells(coboDict.Item(Me.cobo_Name.Value), "V").Value
Me.cobo_DCFreq = .Cells(coboDict.Item(Me.cobo_Name.Value), "W").Value
Me.cobo_OCStatus = .Cells(coboDict.Item(Me.cobo_Name.Value), "X").Value
Me.txt_OCStart = .Cells(coboDict.Item(Me.cobo_Name.Value), "Y").Value
Me.txt_OCEnd = .Cells(coboDict.Item(Me.cobo_Name.Value), "Z").Value
Me.txt_OCAmt = .Cells(coboDict.Item(Me.cobo_Name.Value), "AA").Value
Me.cobo_OCFreq = .Cells(coboDict.Item(Me.cobo_Name.Value), "AB").Value
Me.cobo_CTIStatus = .Cells(coboDict.Item(Me.cobo_Name.Value), "AC").Value
Me.txt_CTIStart = .Cells(coboDict.Item(Me.cobo_Name.Value), "AD").Value
Me.txt_CTIEnd = .Cells(coboDict.Item(Me.cobo_Name.Value), "AE").Value
Me.txt_CTIAmt = .Cells(coboDict.Item(Me.cobo_Name.Value), "AF").Value
Me.cobo_CTIFreq = .Cells(coboDict.Item(Me.cobo_Name.Value), "AG").Value
Me.cobo_CTOStatus = .Cells(coboDict.Item(Me.cobo_Name.Value), "AH").Value
Me.txt_CTOStart = .Cells(coboDict.Item(Me.cobo_Name.Value), "AI").Value
Me.txt_CTOEnd = .Cells(coboDict.Item(Me.cobo_Name.Value), "AJ").Value
Me.txt_CTOAmt = .Cells(coboDict.Item(Me.cobo_Name.Value), "AK").Value
Me.cobo_CTOFreq = .Cells(coboDict.Item(Me.cobo_Name.Value), "AL").Value
End With
End Sub
Private Sub UserForm_Initialize()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws4 As Worksheet
Dim cGender As Range
Dim cPymtFreq As Range
Dim cCIName As Range
Dim cStatus As Range
Set ws1 = ThisWorkbook.Sheets("Info")
Set ws2 = ThisWorkbook.Sheets("Measurements")
Set ws4 = ThisWorkbook.Sheets("Variables")
For Each cGender In ws4.Range("Gender")
With Me.cobo_Gender
.AddItem cGender.Value
End With
Next cGender
For Each cStatus In ws4.Range("Status")
With Me.cobo_Status
.AddItem cStatus.Value
End With
Next cStatus
For Each cPymtFreq In ws4.Range("PymtFreq")
With Me.cobo_DPFreq
.AddItem cPymtFreq.Value
End With
Next cPymtFreq
For Each cPymtFreq In ws4.Range("PymtFreq")
With Me.cobo_DCFreq
.AddItem cPymtFreq.Value
End With
Next cPymtFreq
For Each cPymtFreq In ws4.Range("PymtFreq")
With Me.cobo_OCFreq
.AddItem cPymtFreq.Value
End With
Next cPymtFreq
For Each cPymtFreq In ws4.Range("PymtFreq")
With Me.cobo_CTIFreq
.AddItem cPymtFreq.Value
End With
Next cPymtFreq
For Each cPymtFreq In ws4.Range("PymtFreq")
With Me.cobo_CTOFreq
.AddItem cPymtFreq.Value
End With
Next cPymtFreq
Range("F1").Select
ActiveWorkbook.Worksheets("Info").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Info").Sort.SortFields.Add Key:= _
Range("F2:F8"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Info").Sort.SortFields.Add Key:= _
Range("B2:B8"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("Info").Sort
.SetRange Range("A1:R8")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Set coboDict = CreateObject("Scripting.Dictionary")
With coboDict
For Each cCIName In ws1.Range("CIName")
If Not .exists(cCIName.Value) Then
.Add cCIName.Value, cCIName.Row
Else
If CLng(cCIName.Offset(, -4).Value) > CLng(ws1.Range("B" & .Item(cCIName.Value))) Then
.Item(cCIName.Value) = cCIName.Row
End If
End If
Next cCIName
Me.cobo_Name.List = Application.Transpose(.keys)
End With
End Sub