Update cell value with combo box selection where rows match multiselect listbox selections

accrualguy

New Member
Joined
Jul 2, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. Web
Greetings! I've really hit a wall on this one and hoping someone has a solution.

I have a user form named "MatchForm". There is a multi-select listbox [lstGL] on the form that has nine (9) columns. When a user selects one or more rows in the listbox, along with a value from a combobox [cboStatus], the "Match Entries" command button on the form should update the cell value in column N on worksheet "GL Recon" where the value in column A (on worksheet "GL Recon") is equal to the first column's value of the selected item(s) in the listbox.

Being a multi-select listbox, I realize there will need to be a loop but I honestly don't know where to start.

Thanks in advance for your time and assistance!!!
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
Hi and welcome to MrExcel.

Try this:

VBA Code:
Private Sub CommandButton1_Click()
  Dim i As Long
  Dim f As Range
  Dim sh As Worksheet
  
  If cboStatus.ListIndex = -1 Then
    MsgBox "Select status"
    cboStatus.SetFocus
    Exit Sub
  End If
  
  'update sheet
  Set sh = Sheets("GL Recon")
  For i = 1 To lstGL.ListCount - 1
    If lstGL.Selected(i) Then
      Set f = sh.Range("A:A").Find(lstGL.List(i, 0), , xlValues, xlWhole)
      If Not f Is Nothing Then
        sh.Range("N" & f.Row).Value = cboStatus.Value
      End If
      'unselect row in the listbox
      lstGL.Selected(i) = False
    End If
  Next i
  
  'clear combo
  cboStatus.Value = ""
  
  MsgBox "updated"
End Sub
 

accrualguy

New Member
Joined
Jul 2, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. Web
With one minor tweak, that worked for me! I ended up changing
VBA Code:
 For I = 0
for the sheet update, otherwise it would only update one of the selections.

Tremendous thanks, Dante!
 

Watch MrExcel Video

Forum statistics

Threads
1,114,002
Messages
5,545,440
Members
410,684
Latest member
LakTik
Top