2 column list box to reflect 2 column range

hugletank · Mar 14th 2017

I have a range, a1:b15. Column a has a 15 named skills which will remain constant, column b has an x to signify if the skill has been learnt which I want to be able to change using a userofrm, x to signify the skill is learnt, blank if the skill hasn't been learnt.

i have a userform with list box that shows the same data from this range in 2 columns.

I want to be able to update the data in column b by either selecting or unselcting the relevant skill (x) in column b using the listbox. I'd like to be able to update multiple rows at a time, to either add an x or remove an x as required.

Whats the best way to achieve this?

MickG · Mar 15th 2017

Re: 2 column list box to reflect 2 column range

Try this:-
I think you need to fill the List box as shown below, NOT with rowsource
It does not seem possible to have Multselect set to Multi, but you can select a number individual rows that will be updated as you go.

Code

Option Explicit
Private Sub UserForm_Initialize()
ListBox1.ColumnCount = 2
'Change range to suit !!!
ListBox1.List = Range("A1:B15").Value
ListBox1.MultiSelect = fmMultiSelectSingle
End Sub


Private Sub ListBox1_Click()
Dim n As Long
 With ListBox1
  For n = 0 To .ListCount - 1
  If .Selected(n) Then
   .List(n, 1) = IIf(.List(n, 1) = "x", "", "x")
 End If
 Next n
 End With
End Sub

Display More

hugletank · Apr 17th 2017

Re: 2 column list box to reflect 2 column range

Thanks, that worked a treat.

MickG · Apr 18th 2017

Re: 2 column list box to reflect 2 column range

You're welcome

2 column list box to reflect 2 column range

Participate now!

Share