Checkbox-Listbox Functionality

[jreilly851]

Hi all,

I am working with a checkbox listbox and am having trouble with getting the concept I have planned out of my head and into Excel. The userform has a combobox listed at the top (w/ free-form text entry), followed by the listbox, and finally a command button at the bottom of the form. Here's what I am trying to do:

I have two columns of data. Column A contains a group of data, and column B contains subsets of data to the groups in column A (see below):
Column A Column B
A123 A123456A
A123 A123456B
A123 A123456C
B123 B123456A
B123 B123456B
B123 B123456C

I want to connect a combo box selection to the listbox selection, then tie it all off with a command button. When a user enters code "A123" into the combo box, for example, I want the listbox to populate all column B options available. Below is a step by step of how I want this to work:

User enters "A123" into the combobox
Listbox auto-refreshes to show "A123456A", "A123456B", "A123456C"
User selects options "A123456A", and "A123456B", leaving "A123456C" blank
User clicks a command button which takes the selected values (A123456A and A123456B) and dumps them into a range of cells in another sheet

Any help I can get solving this problem is VERY MUCH appreciated!!!

- Jonathan

 

[ranman256]

in the AFTERUPDATE event of the combobox, you must refill the list box.
combo_afterupdate()
lstbox.clear

'scan list to fill box
range("A2").select
while activecell.value <>

if instr(activecell.value,combobox)= 1 then lstbox.additem activecell.value 'add item to list

activecell.offset(1,0).select 'next row
wend

 

[jreilly851]

Having trouble with the line below - causes an error "Compile Error - Syntax Error." Also, it's highlighted in red, seems I cannot enter the value as "<>"

While activecell.value <>

Thoughts?

 

[MickG]

This is the entire code you need for the Userform
Userfom with Combobox1, & Listbox1 & CommandButton1
Data sent to sheet "Results"

Option Explicit
Dim Dic As Object
Private Sub ComboBox1_Change()
With Me.ListBox1
    .List = Split(Dic.Item(ComboBox1.Value), ",")
End With
End Sub


Private Sub CommandButton1_Click()
Dim n As Long
Dim c As Long
 With Me.ListBox1
  .MultiSelect = fmMultiSelectMulti
  For n = 0 To .ListCount - 1
        If .Selected(n) Then
            c = c + 1
            Sheets("Results").Cells(c, 1) = .List(n)
        End If
   Next n
End With
If c > 1 Then MsgBox "Data copied"
Unload Me
End Sub


Private Sub UserForm_Initialize()
Dim Rng As Range, Dn As Range, n As Long
    Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
        Me.ListBox1.MultiSelect = fmMultiSelectMulti
            Set Dic = CreateObject("scripting.dictionary")
                Dic.CompareMode = vbTextCompare
For Each Dn In Rng
    If Not Dic.Exists(Dn.Value) Then
        Dic.Add Dn.Value, Dn.Offset(, 1)
    Else
        Dic.Item(Dn.Value) = Dic.Item(Dn.Value) & "," & Dn.Offset(, 1)
    End If
Next
Me.ComboBox1.List = Application.Transpose(Dic.keys)
 End Sub

 

[jreilly851]

Sorry Mick, something in your code doesn't like my corporate protections in place - probably should have mentioned this is on a work computer, for work related operations. Your code above resulted in a run-time error 70, permission denied.

Any other thoughts?

 

[MickG]

Do you have the "Rowsource" set in either the Combobox or listBox , if so remove the range address from the Properties Window.

 

[jreilly851]

wow - that did it! Thank you so much, I'm pretty sure this is now working EXACTLY how I envisioned it! I'm going to run with this now for a bit and see how it works out, but so far so good.

Again, thanks so much!

- Jonathan

 

[MickG]

You're welcome