Using check boxes to hide columns

satheo

New Member
Joined
Jun 10, 2014
Messages
29
I have a table with a column of form control check boxes on each row that I want a user to be able to select from and then have those selected rows be the only ones visible in another table (on another sheet).

So example Table 1:
A1A2A3Check Box 1
B1B2B3Check Box 2
C1C2C3Check Box 3
D1D2D3Check Box 4
E1E2E3Check Box 5

Let's say I check boxes 1 and 3, so I'd want only two columns, corresponding to A and C, to be visible. Example Table 2:

TRUETRUETRUETRUE
Title textTitleTitleSummary formula
Title textValue from A1Value from C1Summary formula
Title textValue from A3Value from C3Summary formula

Been trying to salvage together some code but not having any success (I'm a VBA noob, if this can be done without it that would be great, but I don't want to use filters it needs to be really user-friendly).

VBA Code:
Private Sub CommandButton1_Click()

Worksheets("Sheet 2").Columns.Hidden = True

Dim c As Integer
c = 1
For c = 1 To 33
    If Worksheets("Sheet2").Cells(8,c).Value = "TRUE"
      Worksheets("Sheet2").Columns(c).Hidden = False
    End If

End With

Next c

End Sub

Essentially I'm trying to start by hiding all columns in Table 2 (manually have TRUE for the first and last, as those are the title and summary ones), then check each column if that cell is TRUE and if so, unhide it. The row with the TRUE values is row 8, and I have 33 total columns. Right now I'm getting a syntax error on first line, I tried to do this as a module but same issue. The Button is on Sheet 1 along with the check boxes, if that matters (the check boxes and TRUE links work). Thanks!
 
Last edited:

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,821
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Private Sub CommandButton1_Click()
   Dim c As Long

   With Worksheets("Sheet2")
      For c = 1 To 33
          .Columns(c).Hidden = Not .Cells(8, c).Value = True
      Next c
   End With
End Sub
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,122,312
Messages
5,595,427
Members
413,990
Latest member
Kher83

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
Top