asked on

Dynamic control of Items in an Excel multiListBox

I have two ListBoxes (multi-column), each set for multiple selections via the boxes at left-hand end of each item row:

lb_contactsAvailable - gets its item data from ws1 = sheet"List_Collab_Reviewer"
when an item row in lb_contactsAvailable is selected, it is written to the corresponding row in ws2 = sheet"Q_Collab_Reviewer"
when that same item row is DESELECTED, it is deleted from sheet"Q_Collab_Reviewer"

NOW: I want the right hand listBox: lb_contactsAllocated to read the data that appears in sheet"Q_Collab_Reviewer", with blank items where no item exists in the sheet, and the respective Item details where details appear in the sheet. The overall effect at listBox level will be that items selected in the left hand listBox will also appear (opposite) as the corresponding item in the right hand listBox. It will also disappear when deselected in the left hand listBox. (The selection boxes in the right hand list box are for another purpose.)

I THOUGHT that by defining a rowSource range for lb_contactsAllocated that had NO data, then when data appeared in that range, it would appear in lb_contactAllocated. But instead, when I inserted the following code in the initiation event for the userform, the same data appeared in both listBoxes, despite the absence of data in sheet"Q_Collab_Reviewer".

With lb_contactsAllocated
.ColumnCount = 8
.ColumnWidths = "0;55;55;33;33;33;33;55"
.RowSource = ws2.Range(ws2.Cells(2, 1), ws2.Cells(7, 8)).Address
End With

Please note, the number of items in the left hand list box will vary, the value of SIX is illustrative.

At that point, I knew I needed more knowledge.

Many thanks

Kelvin
9.-EEQ--13-feb-2017.xlsm

Shums Faruk

Hi Kelvin,

Please check here. This would help you as RowSource for ListBox is not advisable:
Microsoft Excel: Dynamic ListBox on UserForm

Shums Faruk

For your case, please try below:

With lb_contactsAvailable
        .ColumnCount = 8
        .ColumnWidths = "0;55;55;33;33;33;33;55"
        .RowSource = ws1.Range(Cells(2, 1), ws1.Cells(ws1.Cells(7, 8).CurrentRegion.Rows.Count, 8)).Address
    End With

Open in new window

ASKER CERTIFIED SOLUTION

Shums Faruk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Roger

ASKER

Thanks, Shums:
Your in-file work is very much appreciated. Will get back to you soonest.
Kelvin

Roger

ASKER

Hi Shums:
Followed your method, thanks.
After time to read up, and a couple of slips, I got it working fine.
Referenced cells in Q_Collab_Reviewer, using the CONTA dimensions of the left-hand List_Collab_Reviewer

Many thanks
Kelvin

Shums Faruk

You're Welcome Kevin! Glad I was able to help