Looping through ActiveX checkboxes and adding items to ActiveX List Box

[sllaksvb]

Hi all,

I am pretty new to VBA and wrote many if statements for one of my projects. Here is my sample code:

If Sheets("Sheet1").cb1.Value = True Then
        Sheets("Summary").listmarket.AddItem "United States A"
    End If
   
    If Sheets("Sheet1").cb2.Value = True Then
        Sheets("Summary").listmarket.AddItem "United States B"
    End If

    If Sheets("Sheet1").cb3.Value = True Then
            Sheets("Summary").listmarket.AddItem "United States C"
    End If
   
    If Sheets("Sheet1").cb4.Value = True Then
            Sheets("Summary").listmarket.AddItem "Australia"
    End If
   
    If Sheets("Sheet1").cb5.Value = True Then
            Sheets("Summary").listmarket.AddItem "Austria"
    End If
   
    If Sheets("Sheet1").cb6.Value = True Then
            Sheets("Summary").listmarket.AddItem "Belgium"
    End If
   
    If Sheets("Sheet1").cb7.Value = True Then
            Sheets("Summary").listmarket.AddItem "Canada"
    End If
   
    If Sheets("Sheet1").cb8.Value = True Then
            Sheets("Summary").listmarket.AddItem "Czech Republic"
    End If

    If Sheets("Sheet1").cb9.Value = True Then
            Sheets("Summary").listmarket.AddItem "Denmark"
    End If

    If Sheets("Sheet1").cb10.Value = True Then
            Sheets("Summary").listmarket.AddItem "Finland"
    End If
  
    If Sheets("Sheet1").cb11.Value = True Then
            Sheets("Summary").listmarket.AddItem "France"
    End If

    If Sheets("Sheet1").cb12.Value = True Then
            Sheets("Summary").listmarket.AddItem "Germany"
    End If

The cb# are checkboxes and listmarket is a list box that I've placed on my sheet with the activeX control.

I have several problems with this code that I require help with!
I would like to loop through all the checkboxes in a column and if the value is true, add the value to the list on the other sheet.
Also, I have been trying to refer to a cell instead of adding the countries in " ".

However,

Sheets("Summary").listmarket.AddItem Range("A5").Value

does not seem to be working for me.

The cell that I am trying to refer to is a column to the left of where the checkboxes are. So it might be possible to refer to the value using offset?

Any assistance on this matter is greatly appreciated! Thank you.

[Leith Ross]

Hello sllaksvb,

This macro will use the cell to the right of the checkbox to add to the List object on the "Summary" sheet. The macro expects the checkboxes to fit inside a single cell.

Sub CheckBoxTest()


    Dim cb      As Object
    Dim Cell    As Range
     
            For Each cb In Worksheets("Sheet1").OLEObjects
                If TypeName(cb.Object) = "CheckBox" Then
                    Set Cell = cb.TopLeftCell
                    If cb.Object.Value = True Then
                        Sheets("Summary").listmarket.AddItem Cell.Offset(0, 1).Value
                    End If
                End If
            Next cb
        
End Sub

[sllaksvb]

Hi Leith,

Thank you for your code! I have two sets of code that's supposed to do the same thing.

Your code works perfectly for one set of checkboxes on Sheet1.
However, when I tried to put in the same code for Sheet2, the listbox does not populate. It looks like it is populating in white font (am able to click on items that look blank in the listbox).

By any chance, would you know why it's coming out this way?
Thank you for your help again! Really appreciate it.

[Leith Ross]

Hello sllaksvb,

For the code on "Sheet2", you will need to change this line in the macro...

    ' Current code
    For Each cb In Worksheets("Sheet1").OLEObjects

    ' Replacement code
    For each cb In Worksheets("Sheet2").OLEObjects

The font problem is odd. I don't know what is happening there since the macro does not change the font color.
If you could post your workbook for review, it would make it easier for me to diagnose the problem.

[sllaksvb]

Hi Leith,

I managed to find the problem. I declared two new variables, cb2 as object and cell2 as range, but forgot to change the .additem cell to .additem cell2.

Thank you for your help! Really appreciate it.

[Leith Ross]

Hello sllaksvb,

Good troubleshooting! You're welcome.