Repopulate UserForm ListBox from CheckBox

Re: Repopulate UserForm ListBox from CheckBox

Hi vmandr

Looks like you have got yourself in a bit of a muddle. The code looks to me to be overly complex for what you are attempting to do. Lets get rid of your code and start fresh. Basically if I have read your process correctly, you want to concatenate Cols A abd B if Check box 1 is clicked and you want to concatenate Cols B and C if Check box 2 is clicked. This is what your code was telling me anyways. So here is the code. You naturally will need to make the range and array dynamic but this will get you on your way. I attached your workbook to show workings.

Take care

Smallman

Option Explicit
Public ar As Variant
Public i As Integer


Private Sub CheckBox1_Click()
    i = 1
    Call MySub
End Sub


Private Sub CheckBox2_Click()
    i = 2
    Call MySub
End Sub


Private Sub MySub()
Dim arr(1 To 2) As Variant
ListBox1.Clear
ar = Range(Cells(2, i), Cells(3, i + 1))
    For i = 1 To 2
        arr(i) = ar(i, 1) & "-" & ar(i, 2)
    Next i


ListBox1.List() = arr
End Sub

Re: Repopulate UserForm ListBox from CheckBox

What do you want to happen if both checkboxes are checked at the same time? Or do you want that to not be possible?

This might be something for you....just an extension/modification of Smallman's idea.

forum.ozgrid.com/index.php?attachment/44901/

Re: Repopulate UserForm ListBox from CheckBox

Why not use option buttons, then you won't need code to change the value of one control when the other is clicked?

Also, why not use a multicolumn listbox?

Then you could display both columns of data without having to concatenate them.

If you need the '-' at some point, eg for a search, just add it when it's needed.

Anyway, here's the code for option buttons.

Private Sub OptionButton1_Click()
Dim rng As Range


    Set rng = Range("A1:B3")
    
    ListBox1.List = rng.Value


End Sub


Private Sub OptionButton2_Click()
Dim rng As Range


    Set rng = Range("B1:C3")
    
    ListBox1.List = rng.Value
End Sub

PS If you do need the '-' then that can be added quite easily.

PPS Why are you using End? That will stop all code that's running.

Re: Repopulate UserForm ListBox from CheckBox

Here's some sample code to add the '-'.

Private Sub OptionButton1_Click()
Dim rng As Range
Dim cl As Range


    Set rng = Range("A1:A3")
    
    ListBox1.Clear
    For Each cl In rng
        ListBox1.AddItem cl.Value & " - " & cl.Offset(, 1).Value
    Next cl
    
    
End Sub


Private Sub OptionButton2_Click()
Dim rng As Range
Dim cl As Range


    Set rng = Range("B1:B3")
    
    ListBox1.Clear
    For Each cl In rng
        ListBox1.AddItem cl.Value & " - " & cl.Offset(, 1).Value
    Next cl
    
    
End Sub

Re: Repopulate UserForm ListBox from CheckBox

Option buttons can't be pushed by accident either.

Not sure what you meant about headers, arrays etc.

If you want a header in a listbox you have to use RowSource to populate it from a range.

You don't have to use an array to populate a listbox either, you can use AddItem or you can use List with a range.

If you want data from a different sheet add a worksheet reference for the range in the code. For example.

Private Sub OptionButton1_Click()


    Listbox1.List = Worksheets("Sheet1").Range("A1:B10").Value


End Sub


Private Sub OptionButton2_Click()


    Listbox1.List = Worksheets("Sheet2").Range("A1:B10").Value


End Sub


Private Sub OptionButton3_Click()


    Listbox1.List = Worksheets("Sheet3").Range("A1:B10").Value


End Sub

You might notice Clear isn't used here, it's not needed as List replaces the contents of the listbox not adds to it.

Re: Repopulate UserForm ListBox from CheckBox

Another option would be to use a three column ListBox and have the option buttons control which columns are visible.

Private Sub OptionButton1_Click()
    With ListBox1
        .ColumnWidths = ";;0"
        .BoundColumn = 1
        .TextColumn = 2
    End With
End Sub


Private Sub OptionButton2_Click()
    With ListBox1
        .ColumnWidths = "0;;"
        .BoundColumn = 2
        .TextColumn = 3
    End With
End Sub


Private Sub UserForm_Initialize()
    With ListBox1
        .ColumnCount = 3
        .List = Sheet1.Range("A1:C10").Value
    End With
End Sub