Change column in multi-column listbox

[plsm5882]

Hi.

Not sure this is possible, but here goes:

I have a multi-column multi-select listbox. It's populated from a dynamic range on a sheet (width of the range does not change but number of rows does). The listbox is on a userform with a submit button that write values from the form to another sheet.

2 of the 9 columns are populated with preferred values that the user might choose to change before clicking the submit button.

I have no code to show as I cannot think of a way to do it. If I am right, that it can't be done, does anyone have any ideas as to how this can be solved?

I chose to use the listbox because it's quick for the user to choose the entries needed.

Any help is much appreciated. Thanks.

 

[plsm5882]

Forgot to say that the list values has to be updated from either combo boxes or textboxes and the original source should not be changed from the form. Actually, thinking about it could I simply change the listindex.list(row, col) = combobox.text? Hmmm...

 

[plsm5882]

Ok. So, I can change the values of the first row but I have no idea of how to change the values of the selected row, particularly when more than one row could be selected and the values could be different in each selected row.

I open another userform by double clicking. That userform has 2 combo boxes and a Done button. This is where I need help now please.

Opening the new form:
Private Sub Userform_Initialize()
Me.cboMethod.Text = frmNewPatient.lbVaccines.Selected(frmNewPatient.lbVaccines.ListIndex, 5)
Me.cboSite.Text = frmNewPatient.lbVaccines.Selected(frmNewPatient.lbVaccines.ListIndex, 6)
End Sub

Done button:
Private Sub btnDone_Click()
If Me.lbVaccines.Selected(frmNewPatient.lbVaccines.ListIndex, 5) Then
If Me.cboMethod.Text <> "" And Me.cboMethod.Text <> frmNewPatient.lbVaccines.List(frmNewPatient.lbVaccines.ListIndex, 5) Then
frmNewPatient.lbVaccines.List(frmNewPatient.lbVaccines.ListIndex, 5) = Me.cboMethod.Text
End If

If Me.cboMethod.Text <> "" And Me.cboMethod.Text <> frmNewPatient.lbVaccines.List(frmNewPatient.lbVaccines.ListIndex, 6) Then
frmNewPatient.lbVaccines.List(frmNewPatient.lbVaccines.ListIndex, 6) = Me.cboSite.Text
End If
End If
End Sub

 

[plsm5882]

I assume I need a loop, to loop through the selected entries, but I don't know how to make that work with a simple editing form.

 

[mikerickson]

I'm a bit confused as to what your problem is.
I also notice that you are talking about a multi-select ListBox in the OP, but your code uses cboMethod as a control name, which makes me think of a ComboBox (there is no multi-select combobox)?

If you want to hide all but those two columns in the drop-down, you could use the .ColumnWidths property to set some columns to width 0. This will show the second and fifth columns and hide the rest

cboMethod.ColumnWidths = "0;;0;0;;0;0;0;0"

 

[plsm5882]

Hi Mike. Thanks for replying.

Apologies, I probably confused the issue with too many posts.

I have a multi-column multi-select listbox that I need the user to be able to change some of the values of entries they selected. That's it in a nutshell.

The combo boxes are there because the user has to choose from a list of values they can change the listbox values into (rather than free text).

If it was a single-select listbox it would be easy, but because its not, I can't seem to get my head around it.

 

[mikerickson]

"I have a multi-column multi-select listbox that I need the user to be able to change some of the values of entries they selected. That's it in a nutshell."

This is going to have to be a pretty generic response.

If the user has selected a record in the ListBox and the user want the 3 column of that selection to be "NEW", code like this would be used

With Listbox1
    .List(.ListIndex, 2) = "NEW"
End With

I assume that you have textboxes etc where the user can enter the new values that they want.
The tough part would be designing 1) which column does the user want changed. 2)if there are multiple selections made, which of those should be changed or all and how does the user indicate that.

 

[plsm5882]

Thanks Mike.

I have decided this is too much hassle and I am going force the user to choose a single row from the listbox if they want to make changes. Once they've made their changes they can choose as many rows from the listbox as they want. Thanks again.

 

[mikerickson]

Sounds like a plan.