Use dropdown and checkboxes to unhide columns on different sheet

rikeezzein

New Member
Joined
Nov 21, 2020
Messages
1
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
For the example of my problem, I have a worksheet with a list of football teams on one sheet. On the next sheet is a dropdown list of these teams. There are around 20 total dropdowns that could be filled. Next to each dropdown are checkboxes in columns representing "First," "Second," and "Third." On the third sheet, each team has a column for all three options i.e. Raiders First, Raiders Second, and Raiders Third.

What I have been trying to accomplish is having all of these columns hidden by default, then (for example) the "Raiders Second" column unhidden if a dropdown of "Raiders" is selected with the checkbox in the same row, under the "Second" column marked as TRUE.

The closest I've been able to get before I'm completely overwhelmed is below. And I know there's a good chance that this isn't even on the right path. I am only three days into knowing VBA exists, so take pity!
--------------------------------------------------------------------------
Dim r As Integer, c As Integer, LastCell As Integer

Set LastRow = 22

Set r = 2 to LastRow

Set c = 1



IF sheet4.(r, c) <> “” AND <> “Choose Carrier”, AND IF (r, (c + 1)) = TRUE, then the column on sheet1 with the value of “(r, c) + FIRST” on row 2 is unhidden

IF sheet4.(r, c) <> “” AND <> “Choose Carrier”, AND IF (r, (c + 2)) = TRUE, then the column on sheet1 with the value of “(r, c) + INCREASE” on row 2 is unhidden

IF sheet4.(r, c) <> “” AND <> “Choose Carrier”, AND IF (r, (c + 3)) = TRUE, then the column on sheet1 with the value of “(r, c) + FINAL” on row 2 is unhidden

r = r + 1

------------------------------------------------------------------------------
 

Attachments

  • sheet1.JPG
    sheet1.JPG
    21.9 KB · Views: 3
  • sheet2.JPG
    sheet2.JPG
    83.1 KB · Views: 3
  • sheet3.JPG
    sheet3.JPG
    131.5 KB · Views: 3

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Hello rikeezzein, welcome.
As first the column names in the sheet2 must to match combination of the value behind checkbox in that row
and words in the column header in the sheet1. Take care when naming headers in the sheet2.
Insert this code in the editor window (sheet1).
VBA Code:
Option Explicit

Dim varColumnName As String
Dim varColumnNameFind
Dim varColumn As Integer
                
Private Sub CheckBox1_Click()
        
        varColumnName = Sheets("Sheet1").Range("A2").Value & " " & _
        Sheets("Sheet1").Range("B1").Value
        Set varColumnNameFind = Sheets("Sheet2").Rows(1).Find(varColumnName)
        varColumn = varColumnNameFind.Column
        If CheckBox1.Value = True And Not varColumnNameFind Is Nothing Then
                Sheets("Sheet2").Columns(varColumn).EntireColumn.Hidden = True
        Else
                Sheets("Sheet2").Columns(varColumn).EntireColumn.Hidden = False
        End If
     
End Sub
Create subrutine for each checkbox that looks like this one but take care about ranges in this lines
VBA Code:
varColumnName = Sheets("Sheet1").Range("A2").Value & " " & _
        Sheets("Sheet1").Range("B1").Value
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

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