Add checkboxes to Listbox

G

gaudrco

Board Regular
Joined
Aug 16, 2019
Messages
203
I have many checkboxes that I would like to move to a listbox to make things look neater in my spreadsheet. I have never used the listbox but I would like it to include all of my checkboxes with the option to choose multiple checkboxes. I know I must change the properties of the listbox
[ListStyle = 1 - fmListStyleOption] and [MultiSelect = 1 - fmMultiSelectMulti]. Here is the code of my checkboxes:

Note: CheckBox1 - Checkbox34 are the same code structure; Checkbox35 is a 'Select/Deselect All' function for Checkboxes 1-34
Code: 
Private Sub CheckBox1_Click()
Dim xAddress As String
xAddress = "D"
If CheckBox1.Value Then
    Application.ActiveSheet.Columns(xAddress).Hidden = False
    CheckBox1.Caption = Range("D7").Value
Else
    Application.ActiveSheet.Columns(xAddress).Hidden = True
    CheckBox1.Caption = Range("D7").Value
End If
End Sub


Private Sub CheckBox2_Click()
Dim xAddress As String
xAddress = "E"
If CheckBox2.Value Then
    Application.ActiveSheet.Columns(xAddress).Hidden = False
    CheckBox2.Caption = Range("E7").Value
Else
    Application.ActiveSheet.Columns(xAddress).Hidden = True
    CheckBox2.Caption = Range("E7").Value
End If
End Sub


Private Sub CheckBox3_Click()
Dim xAddress As String
xAddress = "F"
If CheckBox3.Value Then
    Application.ActiveSheet.Columns(xAddress).Hidden = False
    CheckBox3.Caption = Range("F7").Value
Else
    Application.ActiveSheet.Columns(xAddress).Hidden = True
    CheckBox3.Caption = Range("F7").Value
End If
End Sub


Private Sub CheckBox4_Click()
Dim xAddress As String
xAddress = "G"
If CheckBox4.Value Then
    Application.ActiveSheet.Columns(xAddress).Hidden = False
    CheckBox4.Caption = Range("G7").Value
Else
    Application.ActiveSheet.Columns(xAddress).Hidden = True
    CheckBox4.Caption = Range("G7").Value
End If
End Sub


Private Sub CheckBox5_Click()
Dim xAddress As String
xAddress = "H"
If CheckBox5.Value Then
    Application.ActiveSheet.Columns(xAddress).Hidden = False
    CheckBox5.Caption = Range("H7").Value
Else
    Application.ActiveSheet.Columns(xAddress).Hidden = True
    CheckBox5.Caption = Range("H7").Value
End If
End Sub


Private Sub CheckBox6_Click()
Dim xAddress As String
xAddress = "I"
If CheckBox6.Value Then
    Application.ActiveSheet.Columns(xAddress).Hidden = False
    CheckBox6.Caption = Range("I7").Value
Else
    Application.ActiveSheet.Columns(xAddress).Hidden = True
    CheckBox6.Caption = Range("I7").Value
End If
End Sub


Private Sub CheckBox7_Click()
Dim xAddress As String
xAddress = "J"
If CheckBox7.Value Then
    Application.ActiveSheet.Columns(xAddress).Hidden = False
    CheckBox7.Caption = Range("J7").Value
Else
    Application.ActiveSheet.Columns(xAddress).Hidden = True
    CheckBox7.Caption = Range("J7").Value
End If
End Sub


Private Sub CheckBox8_Click()
Dim xAddress As String
xAddress = "K"
If CheckBox8.Value Then
    Application.ActiveSheet.Columns(xAddress).Hidden = False
    CheckBox8.Caption = Range("K7").Value
Else
    Application.ActiveSheet.Columns(xAddress).Hidden = True
    CheckBox8.Caption = Range("K7").Value
End If
End Sub


Private Sub CheckBox9_Click()
Dim xAddress As String
xAddress = "L"
If CheckBox9.Value Then
    Application.ActiveSheet.Columns(xAddress).Hidden = False
    CheckBox9.Caption = Range("L7").Value
Else
    Application.ActiveSheet.Columns(xAddress).Hidden = True
    CheckBox9.Caption = Range("L7").Value
End If
End Sub


Private Sub CheckBox10_Click()
Dim xAddress As String
xAddress = "M"
If CheckBox10.Value Then
    Application.ActiveSheet.Columns(xAddress).Hidden = False
    CheckBox10.Caption = Range("M7").Value
Else
    Application.ActiveSheet.Columns(xAddress).Hidden = True
    CheckBox10.Caption = Range("M7").Value
End If
End Sub


Private Sub CheckBox11_Click()
Dim xAddress As String
xAddress = "N"
If CheckBox11.Value Then
    Application.ActiveSheet.Columns(xAddress).Hidden = False
    CheckBox11.Caption = Range("N7").Value
Else
    Application.ActiveSheet.Columns(xAddress).Hidden = True
    CheckBox11.Caption = Range("N7").Value
End If
End Sub


Private Sub CheckBox12_Click()
Dim xAddress As String
xAddress = "O"
If CheckBox12.Value Then
    Application.ActiveSheet.Columns(xAddress).Hidden = False
    CheckBox12.Caption = Range("O7").Value
Else
    Application.ActiveSheet.Columns(xAddress).Hidden = True
    CheckBox12.Caption = Range("O7").Value
End If
End Sub


Private Sub CheckBox13_Click()
Dim xAddress As String
xAddress = "P"
If CheckBox13.Value Then
    Application.ActiveSheet.Columns(xAddress).Hidden = False
    CheckBox13.Caption = Range("P7").Value
Else
    Application.ActiveSheet.Columns(xAddress).Hidden = True
    CheckBox13.Caption = Range("P7").Value
End If
End Sub


Private Sub CheckBox14_Click()
Dim xAddress As String
xAddress = "Q"
If CheckBox14.Value Then
    Application.ActiveSheet.Columns(xAddress).Hidden = False
    CheckBox14.Caption = Range("Q7").Value
Else
    Application.ActiveSheet.Columns(xAddress).Hidden = True
    CheckBox14.Caption = Range("Q7").Value
End If
End Sub


Private Sub CheckBox15_Click()
Dim xAddress As String
xAddress = "R"
If CheckBox15.Value Then
    Application.ActiveSheet.Columns(xAddress).Hidden = False
    CheckBox15.Caption = Range("R7").Value
Else
    Application.ActiveSheet.Columns(xAddress).Hidden = True
    CheckBox15.Caption = Range("R7").Value
End If
End Sub
Private Sub CheckBox16_Click()
Dim xAddress As String
xAddress = "S"
If CheckBox16.Value Then
    Application.ActiveSheet.Columns(xAddress).Hidden = False
    CheckBox16.Caption = Range("S7").Value
Else
    Application.ActiveSheet.Columns(xAddress).Hidden = True
    CheckBox16.Caption = Range("S7").Value
End If
End Sub


Private Sub CheckBox17_Click()
Dim xAddress As String
xAddress = "T"
If CheckBox17.Value Then
    Application.ActiveSheet.Columns(xAddress).Hidden = False
    CheckBox17.Caption = Range("T7").Value
Else
    Application.ActiveSheet.Columns(xAddress).Hidden = True
    CheckBox17.Caption = Range("T7").Value
End If
End Sub


Private Sub CheckBox18_Click()
Dim xAddress As String
xAddress = "U"
If CheckBox18.Value Then
    Application.ActiveSheet.Columns(xAddress).Hidden = False
    CheckBox18.Caption = Range("U7").Value
Else
    Application.ActiveSheet.Columns(xAddress).Hidden = True
    CheckBox18.Caption = Range("U7").Value
End If
End Sub


Private Sub CheckBox19_Click()
Dim xAddress As String
xAddress = "V"
If CheckBox19.Value Then
    Application.ActiveSheet.Columns(xAddress).Hidden = False
    CheckBox19.Caption = Range("V7").Value
Else
    Application.ActiveSheet.Columns(xAddress).Hidden = True
    CheckBox19.Caption = Range("V7").Value
End If
End Sub


Private Sub CheckBox20_Click()
Dim xAddress As String
xAddress = "W"
If CheckBox20.Value Then
    Application.ActiveSheet.Columns(xAddress).Hidden = False
    CheckBox20.Caption = Range("W7").Value
Else
    Application.ActiveSheet.Columns(xAddress).Hidden = True
    CheckBox20.Caption = Range("W7").Value
End If
End Sub


Private Sub CheckBox21_Click()
Dim xAddress As String
xAddress = "X"
If CheckBox21.Value Then
    Application.ActiveSheet.Columns(xAddress).Hidden = False
    CheckBox21.Caption = Range("X7").Value
Else
    Application.ActiveSheet.Columns(xAddress).Hidden = True
    CheckBox21.Caption = Range("X7").Value
End If
End Sub


Private Sub CheckBox22_Click()
Dim xAddress As String
xAddress = "Y"
If CheckBox22.Value Then
    Application.ActiveSheet.Columns(xAddress).Hidden = False
    CheckBox22.Caption = Range("Y7").Value
Else
    Application.ActiveSheet.Columns(xAddress).Hidden = True
    CheckBox22.Caption = Range("Y7").Value
End If
End Sub


Private Sub CheckBox23_Click()
Dim xAddress As String
xAddress = "Z"
If CheckBox23.Value Then
    Application.ActiveSheet.Columns(xAddress).Hidden = False
    CheckBox23.Caption = Range("Z7").Value
Else
    Application.ActiveSheet.Columns(xAddress).Hidden = True
    CheckBox23.Caption = Range("Z7").Value
End If
End Sub


Private Sub CheckBox24_Click()
Dim xAddress As String
xAddress = "AA"
If CheckBox24.Value Then
    Application.ActiveSheet.Columns(xAddress).Hidden = False
    CheckBox24.Caption = Range("AA7").Value
Else
    Application.ActiveSheet.Columns(xAddress).Hidden = True
    CheckBox24.Caption = Range("AA7").Value
End If
End Sub


Private Sub CheckBox25_Click()
Dim xAddress As String
xAddress = "AB"
If CheckBox25.Value Then
    Application.ActiveSheet.Columns(xAddress).Hidden = False
    CheckBox25.Caption = Range("AB7").Value
Else
    Application.ActiveSheet.Columns(xAddress).Hidden = True
    CheckBox25.Caption = Range("AB7").Value
End If
End Sub


Private Sub CheckBox26_Click()
Dim xAddress As String
xAddress = "AC"
If CheckBox26.Value Then
    Application.ActiveSheet.Columns(xAddress).Hidden = False
    CheckBox26.Caption = Range("AC7").Value
Else
    Application.ActiveSheet.Columns(xAddress).Hidden = True
    CheckBox26.Caption = Range("AC7").Value
End If
End Sub


Private Sub CheckBox27_Click()
Dim xAddress As String
xAddress = "AD"
If CheckBox27.Value Then
    Application.ActiveSheet.Columns(xAddress).Hidden = False
    CheckBox27.Caption = Range("AD7").Value
Else
    Application.ActiveSheet.Columns(xAddress).Hidden = True
    CheckBox27.Caption = Range("AD7").Value
End If
End Sub


Private Sub CheckBox28_Click()
Dim xAddress As String
xAddress = "AE"
If CheckBox28.Value Then
    Application.ActiveSheet.Columns(xAddress).Hidden = False
    CheckBox28.Caption = Range("AE7").Value
Else
    Application.ActiveSheet.Columns(xAddress).Hidden = True
    CheckBox28.Caption = Range("AE7").Value
End If
End Sub


Private Sub CheckBox29_Click()
Dim xAddress As String
xAddress = "AF"
If CheckBox29.Value Then
    Application.ActiveSheet.Columns(xAddress).Hidden = False
    CheckBox29.Caption = Range("AF7").Value
Else
    Application.ActiveSheet.Columns(xAddress).Hidden = True
    CheckBox29.Caption = Range("AF7").Value
End If
End Sub


Private Sub CheckBox30_Click()
Dim xAddress As String
xAddress = "AG"
If CheckBox30.Value Then
    Application.ActiveSheet.Columns(xAddress).Hidden = False
    CheckBox30.Caption = Range("AG7").Value
Else
    Application.ActiveSheet.Columns(xAddress).Hidden = True
    CheckBox30.Caption = Range("AG7").Value
End If
End Sub


Private Sub CheckBox31_Click()
Dim xAddress As String
xAddress = "AH"
If CheckBox31.Value Then
    Application.ActiveSheet.Columns(xAddress).Hidden = False
    CheckBox31.Caption = Range("AH7").Value
Else
    Application.ActiveSheet.Columns(xAddress).Hidden = True
    CheckBox31.Caption = Range("AH7").Value
End If
End Sub


Private Sub CheckBox32_Click()
Dim xAddress As String
xAddress = "AI"
If CheckBox32.Value Then
    Application.ActiveSheet.Columns(xAddress).Hidden = False
    CheckBox32.Caption = Range("AI7").Value
Else
    Application.ActiveSheet.Columns(xAddress).Hidden = True
    CheckBox32.Caption = Range("AI7").Value
End If
End Sub


Private Sub CheckBox33_Click()
Dim xAddress As String
xAddress = "AJ"
If CheckBox33.Value Then
    Application.ActiveSheet.Columns(xAddress).Hidden = False
    CheckBox33.Caption = Range("AJ7").Value
Else
    Application.ActiveSheet.Columns(xAddress).Hidden = True
    CheckBox33.Caption = Range("AJ7").Value
End If
End Sub


Private Sub CheckBox34_Click()
Dim xAddress As String
xAddress = "AK"
If CheckBox34.Value Then
    Application.ActiveSheet.Columns(xAddress).Hidden = False
    CheckBox34.Caption = Range("AK7").Value
Else
    Application.ActiveSheet.Columns(xAddress).Hidden = True
    CheckBox34.Caption = Range("AK7").Value
End If
End Sub


Private Sub CheckBox35_Click()
Dim xAddress As String
xAddress = "AL"
If CheckBox35.Value Then
    Application.ActiveSheet.Columns(xAddress).Hidden = False
    CheckBox35.Caption = Range("AL7").Value
Else
    Application.ActiveSheet.Columns(xAddress).Hidden = True
    CheckBox35.Caption = Range("AL7").Value
End If
End Sub






Private Sub CheckBox37_Click()
    If Sheets("Competitor Comparison").CheckBox37.Value = True Then
        For i = 1 To 35
            Sheets("Competitor Comparison").OLEObjects("CheckBox" & i).Object.Value = True
        Next i
    Else
        For i = 1 To 35
            Sheets("Competitor Comparison").OLEObjects("CheckBox" & i).Object.Value = False
        Next i
    End If
End Sub
 
Sort by date Sort by votes
I suggest you return to the original request and put the checkboxes in the listbox.
 
Upvote 0

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Fair point. I actually was not able to figure this last part. If I can get this right then I should be all set
gaudrco - Another thing, my last checkbox (CheckBox37) is coded as a Select/Deselect all checkbox but it was not included in the listbox. How do I include that?
DanteAmor - I guess that code should continue to be part of a separate checkbox to the llistbox.
Click to expand...
 
Upvote 0
Try this whit userform

Change CheckBox1 for the name of your checkbox.

Code: 
Private Sub[COLOR=#ff0000] CheckBox1[/COLOR]_Click()
  Dim i As Long
  Application.ScreenUpdating = False
  For i = 0 To ListBox1.ListCount - 1
    ListBox1.Selected(i) = [COLOR=#ff0000]CheckBox1[/COLOR]
  Next
  Application.ScreenUpdating = True
End Sub


Private Sub ListBox1_Change()
  Dim i As Long
  Application.ScreenUpdating = False
  For i = 0 To ListBox1.ListCount - 1
    ActiveSheet.Columns(i + 4).Hidden = ListBox1.Selected(i)
  Next
  Application.ScreenUpdating = True
End Sub


Private Sub UserForm_Activate()
  Dim i As Long, col As String
  For i = Columns("D").Column To Columns("AL").Column
    ListBox1.AddItem Cells(7, i).Value
  Next
End Sub
 
Upvote 0
Im glad to help you, thanks for the feedback.
 
Upvote 0
I was testing out the alternative of using a Userform and was able to successfully add functioning checkboxes to it with a Select/Deselect all checkbox. The only issue I'm having might be a simple one but I'm not sure how to fix it. When I click the button to open the userform, I see all my checkboxes listed as "Checkbox1", "Checkbox2", "Checkbox3", etc. When I click them, they change to the name I want; "Apple", "Orange", "Pear". But if I close the userform and open it again, they all revert back to "Checkbox1", "Checkbox2", "Checkbox3". How do I make it so that the names stay what they are assigned to be when I open and close the userform?
 
Upvote 0
You have to name them at the start in the activate event
 
Upvote 0
DanteAmor said:
You have to name them at the start in the activate event
Click to expand...

How do you do that? Would it go here in the macro with the Userform button activation?
Code: 
Private Sub CommandButton4_Click()

UserForm1.Show

End Sub
 
Last edited:
Upvote 0
In this event:

Code: 
[COLOR=#0000ff]Private Sub UserForm_Activate()[/COLOR]
  Dim i As Long, col As String
  For i = Columns("D").Column To Columns("AL").Column
    ListBox1.AddItem Cells(7, i).Value
  Next
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Solved
Hide/unhide non consecutive rows with toggle button
Replies
4
Views
581
Akuini
Akuini
J
  • Solved
Creating a VBA toggle button to show/hide multiple adjacent and non-adjacent columns in Excel
Replies
4
Views
747
dmt32
D
S
  • Solved
Toggle Button caption dynamicly changed
Replies
2
Views
799
steni
S
M
  • Solved
Toggle Button - hide / show rows
Replies
3
Views
728
Fluff
Fluff
D
  • Question
Toggle for Hiding or Unhiding a Few Specific or Named Rows
2
Replies
10
Views
2K
My Aswer Is This
My Aswer Is This

Forum statistics

Threads
1,214,833
Messages
6,121,865
Members
449,052
Latest member
Fuddy_Duddy

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top