Updating Current Row from ComboBox

reberryjr

Active Member
Joined
Mar 16, 2017
Messages
499
Office Version
  1. 2013
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,387
Office Version
  1. 2019
Platform
  1. Windows
Hi,
untested but see if update to your code will does what you want

Code:
Private Sub cmd_Submit_Click()
    Dim ws1 As Worksheet
    Dim i As Long
    Dim m As Variant
    
    
    Set ws1 = ThisWorkbook.Sheets("Bios")
    
'check if record exists
    m = Application.Match(Me.cobo_Name.Value, ws1.Columns("G"), False)
    
    If Not IsError(m) Then
    
'update existing record
    
        ws1.Range("B" & CLng(m)).Value = CDate(Me.txt_Updated)
    Else
    
'add new record
    
        'your code here
        
    End If




End Sub

Dave
 

Watch MrExcel Video

Forum statistics

Threads
1,123,206
Messages
5,600,317
Members
414,376
Latest member
NickYOW

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top