Excel VBA Userform multi-select listbox (edit/update)

[cerberus1845]

Hi,

Hoping someone can help - I've looked through this forum and can't really find an answer. I've also searched on YoutTube and have found a video which shows EXACTLY what I'm looking for - but the author says he'll provide it in a follow up video (which he either never posts or has subsequently been deleted) - here is a link to the video showing what I want to achieve:

Basically have a userform with a multi-select listbox where you can select multiple values and then select a new value from a dropdown list/combobox and then when a command button is pressed then the new value will be updated to the source sheet/range.. and I'm not sure how to achieve this (but the above video suggest that it is possible)..

Any help or guidance would be greatly appreciated!!

 

[cerberus1845]

does anyone have any ideas around this?.. I know it's possible from the video i attached earlier.. but can't seem to find anything online about how to actually do it..

the requirements are:
1. I have a listbox populate with data
2. there is a dropdown/combo box i can select a new value for one of the columns in the listbox
3. a command button is used to update both the listbox value and the spreadsheet value it relates to

 

[Akuini]

Could you please upload a sample workbook (without sensitive data) to a file-sharing site like Dropbox.com or Google Drive, and then share the link here? Also, ensure that the link is accessible to anyone.

 

[cerberus1845]

Could you please upload a sample workbook (without sensitive data) to a file-sharing site like Dropbox.com or Google Drive, and then share the link here? Also, ensure that the link is accessible to anyone.

I don't have a example to upload.. I literally just want to know how to do the above in the video (select a new value which updates multiple selected listbox items and their corresponding rows in the spreadsheet) - not started it yet.. trying to get the knowledge first

 

[cerberus1845]

ok - I've worked out how to do this now and will share the code tomorrow..

 

[cerberus1845]

ok - so here's the code that I have just now (which currently works as per the video) i.e. there is a populated listbox and two comboboxes.. you can make selections in the listbox and then select values in the comboboxes and then press the Update button and the values update on both the listbox and the source worksheet - I'm not sure if there is a better or more elegant way of writing this i.e. I know it uses 'rowsource' which can be a bit flakey? -so wondered if anyone knew of a better way?

Sub massupdate()
Dim SelectedRows()
idx = 0
ReDim SelectedRows(1 To 1)
With Me.ListBox1 'name of listbox
  For i = 0 To .ListCount - 1
    If .Selected(i) Then
      idx = idx + 1
      ReDim Preserve SelectedRows(1 To idx)
      SelectedRows(idx) = i
    End If
  Next i
  Set yyy = Range(.RowSource) 
  cmbRecruiterEmplID = cmbRecruiterEmplID.Value 'recruiter employee id and recruiter name are linked 
  cmbRecruiterName = cmbRecruiterName.Value 'recruiter name and recruiter employee id are linked 
   
For Each SelectedRow In SelectedRows
   yyy.Cells(SelectedRow + 1, 13).Value = cmbRecruiterEmplID 'for each selection made in the listbox - update the recruiter emplid selected in the combobox to 13th column in spreadsheet
   yyy.Cells(SelectedRow + 1, 14).Value = cmbRecruiterName  'for each selection made in the listbox - update the recruiter employeename selected in the combobox to 14th column in spreadsheet
  Next SelectedRow 'make updates for each selection in listbox

End With