I have a UserForm that pulls values into a combo box from a dynamic range. On some sheets, I want to add data from the UserForm, to the last row. I can do that. I'm struggling to update the row that has the same value as what was selected in the combo box. I've looked around and I found this IF statement, but I'm getting a 1004 error. I wasn't sure if all of the code would be needed or not, so I thought it better to provide it all.
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 i As Long
Set ws1 = ThisWorkbook.Sheets("Bios")
'If ws1.Range("G") = Me.cobo_Name.Value Then
' ws1.Range("B") = CDate(Me.txt_Updated)
'End If
End Sub
Private Sub cobo_Name_Change()
With Sheets("Bios")
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
End With
End Sub
Private Sub UserForm_Initialize()
Dim cGender As Range
Dim cPymtFreq As Range
Dim cEntryType As Range
Dim cStatus As Range
Dim cBiosName As Range
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Dim ws4 As Worksheet
Dim ws5 As Worksheet
Dim LastRow As Long
Dim LastRow2 As Long
Dim LastRow3 As Long
Dim LastRow4 As Long
Set ws1 = ThisWorkbook.Sheets("Bios")
Set ws2 = ThisWorkbook.Sheets("Stats")
Set ws3 = ThisWorkbook.Sheets("Services")
Set ws4 = ThisWorkbook.Sheets("Payments")
Set ws5 = ThisWorkbook.Sheets("Variables")
LastRow = ws1.Range("G" & Rows.Count).End(xlUp).Row + 1
LastRow2 = ws2.Range("G" & Rows.Count).End(xlUp).Row + 1
LastRow3 = ws3.Range("G" & Rows.Count).End(xlUp).Row + 1
LastRow4 = ws4.Range("G" & Rows.Count).End(xlUp).Row + 1
For Each cGender In ws5.Range("Gender")
With Me.cobo_Gender
.AddItem cGender.Value
End With
Next cGender
For Each cStatus In ws5.Range("Status")
With Me.cobo_Status
.AddItem cStatus.Value
End With
Next cStatus
For Each cPymtFreq In ws5.Range("PymtFreq")
With Me.cobo_DPFreq
.AddItem cPymtFreq.Value
End With
Next cPymtFreq
For Each cPymtFreq In ws5.Range("PymtFreq")
With Me.cobo_DCFreq
.AddItem cPymtFreq.Value
End With
Next cPymtFreq
For Each cPymtFreq In ws5.Range("PymtFreq")
With Me.cobo_OCFreq
.AddItem cPymtFreq.Value
End With
Next cPymtFreq
For Each cPymtFreq In ws5.Range("PymtFreq")
With Me.cobo_CTIFreq
.AddItem cPymtFreq.Value
End With
Next cPymtFreq
For Each cPymtFreq In ws5.Range("PymtFreq")
With Me.cobo_CTOFreq
.AddItem cPymtFreq.Value
End With
Next cPymtFreq
ws1.Select
ws1.Range("A2:M" & LastRow).Select
ActiveWorkbook.Worksheets("Bios").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Bios").Sort.SortFields.Add Key:=Range( _
"G2:G" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Bios").Sort.SortFields.Add Key:=Range( _
"B2:B" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveSheet.Sort
.SetRange Range("A2:M" & LastRow)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ws2.Select
ws2.Range("A2:R" & LastRow2).Select
ActiveWorkbook.Worksheets("Stats").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Stats").Sort.SortFields.Add Key:=Range( _
"G2:G" & LastRow2), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Stats").Sort.SortFields.Add Key:=Range( _
"B2:B" & LastRow2), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveSheet.Sort
.SetRange Range("A2:R" & LastRow2)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ws3.Select
ws3.Range("A2:AK" & LastRow3).Select
ActiveWorkbook.Worksheets("Services").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Services").Sort.SortFields.Add Key:=Range( _
"G2:G" & LastRow3), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Services").Sort.SortFields.Add Key:=Range( _
"B2:B" & LastRow3), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveSheet.Sort
.SetRange Range("A2:AK" & LastRow3)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ws4.Select
ws4.Range("A2:G" & LastRow4).Select
ActiveWorkbook.Worksheets("Payments").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Payments").Sort.SortFields.Add Key:=Range( _
"G2:G" & LastRow4), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Payments").Sort.SortFields.Add Key:=Range( _
"B2:B" & LastRow4), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveSheet.Sort
.SetRange Range("A2:G" & LastRow4)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Set coboDict = CreateObject("Scripting.Dictionary")
With coboDict
For Each cBiosName In ws1.Range("BiosName")
If Not .exists(cBiosName.Value) Then
.Add cBiosName.Value, cBiosName.Row
Else
If CLng(cBiosName.Offset(, -5).Value) > CLng(ws1.Range("B" & .Item(cBiosName.Value))) Then
.Item(cBiosName.Value) = cBiosName.Row
End If
End If
Next cBiosName
Me.cobo_Name.List = Application.Transpose(.keys)
End With
End Sub
