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.