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
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Sort by date Sort by votes
Try this

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

[COLOR=#0000ff]'If you are in a userfor, this is to load the letters of the columns in the listbox.[/COLOR]
Private Sub UserForm_Activate()
  Dim i As Long, col As String
  For i = Columns("D").Column To Columns("AL").Column
    col = Evaluate("=SUBSTITUTE(ADDRESS(1," & i & ",4),""1"","""")")
    ListBox1.AddItem col
  Next
End Sub
 
Upvote 0
DanteAmor said:
Try this

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

[COLOR=#0000ff]'If you are in a userfor, this is to load the letters of the columns in the listbox.[/COLOR]
Private Sub UserForm_Activate()
  Dim i As Long, col As String
  For i = Columns("D").Column To Columns("AL").Column
    col = Evaluate("=SUBSTITUTE(ADDRESS(1," & i & ",4),""1"","""")")
    ListBox1.AddItem col
  Next
End Sub
Click to expand...

I tried it but I wasn't really sure how to apply it. How exactly do I use this code to achieve what I'm trying to do? I'm trying to use the ActiveX Listbox (not userform) to create a list of checkboxes that I can assign my checkbox macros to
 
Last edited:
Upvote 0
gaudrco said:
I tried it but I wasn't really sure how to apply it. How exactly do I use this code to achieve what I'm trying to do? I'm trying to use the ActiveX Listbox (not userform) to create a list of checkboxes that I can assign my checkbox macros to
Click to expand...

Try this

Put the following code instead of your code.
Select another sheet, return to the sheet that contains the listbox (to load the letters in the listbox).
Now select any column in the listbox.


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


Private Sub Worksheet_Activate()
  Dim i As Long, col As String
  For i = Columns("D").Column To Columns("AL").Column
    col = Evaluate("=SUBSTITUTE(ADDRESS(1," & i & ",4),""1"","""")")
    ListBox1.AddItem col
  Next
End Sub
 
Upvote 0
Thank you this worked. Is there a way to change the line item titles. When I did this, the checkboxes became the column letter but the way macros are coded, they use the name of specific cell location. So the titles I want are from cell D7:AL7.

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?

Thanks
 
Upvote 0
What about using a Microsoft Forms 2.0 Frame (Insert>ActiveX>More Controls>Microsoft Forms 2.0 Frame)

I could embed the checkboxes in the frame and add a scrollbar. Do you have any idea how to do this with my existing code?
 
Upvote 0
gaudrco said:
Thank you this worked. Is there a way to change the line item titles. When I did this, the checkboxes became the column letter but the way macros are coded, they use the name of specific cell location. So the titles I want are from cell D7:AL7.
Click to expand...


Try this

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


Private Sub Worksheet_Activate()
  Dim i As Long, col As String
[COLOR=#0000ff]  ListBox1.Clear[/COLOR]
[COLOR=#0000ff]  For i = Columns("D").Column To Columns("AL").Column[/COLOR]
[COLOR=#0000ff]    ListBox1.AddItem Cells(7, i).Value[/COLOR]
[COLOR=#0000ff]  Next[/COLOR]
End Sub


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?
Click to expand...

I guess that code should continue to be part of a separate checkbox to the llistbox.
 
Upvote 0
gaudrco said:
What about using a Microsoft Forms 2.0 Frame (Insert>ActiveX>More Controls>Microsoft Forms 2.0 Frame)

I could embed the checkboxes in the frame and add a scrollbar. Do you have any idea how to do this with my existing code?
Click to expand...


- Draw a frame by selecting the More Controls icon in lower left corner of the ActiveX controls in the top menu.
- Select Microsoft Frame 2.0. Draw frame.
- Be in Design Mode, Then select the Frame.
- Change properties:
* KeepScrollBarsVisible = 2 - fmScrollBarsVertical
* ScrollBars = 2 - fmScrollBarsVertical
 * ScrollHeight = 400
b2dc9ce890cc897414d695484c343173.jpg



- Right click on the frame.
- On the pop-up select Frame Object then Edit.
- When you click Edit, you see all the Active X control.
- Now select one and put in your frame.

ec7baef4aa9dab2985b940ebf7c9f8bf.jpg



But you will have to investigate the code to use the checkboxes.
Hopefully someone can help you with this part.
 
Upvote 0
DanteAmor said:


But you will have to investigate the code to use the checkboxes.
Hopefully someone can help you with this part.
Click to expand...

I did some research and it doesn't seem to be possible to do it this way. I think I'm just going to use a regular Userform. So I created a UserForm and added 36 new checkboxes. Then I copied my existing code into the UserForm code box.
Code: 
[COLOR=#333333]Private Sub CheckBox1_Click()[/COLOR]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 [COLOR=#333333]End Sub[/COLOR]

Then I added a button that opens up the UserForm (UserForm1)
When I click the button, the form opens and I can see all my checkboxes but the names of them are Checkbox1, Checkbox2, Checkbox3, etc. When I click the checkbox, the same changes to the appropriate name that intended it to be.
The last checkbox does not work. This checkbox is suppose to Select/Deselect all. I think this has to do with the object type (being an OLEObject) but I'm not certain

How do I get the names of my checkboxes to appear when I click the button that opens UserForm1?
How do I get the Select/Deselect all button to work?

Thanks
 
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
829
Akuini
Akuini
M
  • Solved
show hide multiple columns
Replies
7
Views
398
dmt32
D
J
  • Solved
Creating a VBA toggle button to show/hide multiple adjacent and non-adjacent columns in Excel
Replies
4
Views
1K
dmt32
D
S
  • Solved
Toggle Button caption dynamicly changed
Replies
2
Views
932
steni
S
M
  • Solved
Toggle Button - hide / show rows
Replies
3
Views
891
Fluff
Fluff

Forum statistics

Threads
1,224,817
Messages
6,181,148
Members
453,021
Latest member
Justyna P

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