Change column in multi-column listbox

plsm5882

New Member
Joined
Apr 22, 2016
Messages
20
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.
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

plsm5882

New Member
Joined
Apr 22, 2016
Messages
20
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

New Member
Joined
Apr 22, 2016
Messages
20
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
 
Last edited:

plsm5882

New Member
Joined
Apr 22, 2016
Messages
20
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,971

ADVERTISEMENT

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

VBA Code:
cboMethod.ColumnWidths = "0;;0;0;;0;0;0;0"
 

plsm5882

New Member
Joined
Apr 22, 2016
Messages
20
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,971

ADVERTISEMENT

"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

VBA Code:
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

New Member
Joined
Apr 22, 2016
Messages
20
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,732
Messages
5,626,566
Members
416,190
Latest member
plee3

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