Hide/Unhide check box macro

F

funkykayli

Board Regular
Joined
Apr 25, 2007
Messages
183
I have a user form which has a check box on it. When the check box is = to true I want a column to be unhidden on a sheet in a workbook. When it is = false I want that column to be hidden. I have this code:

Option Explicit

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
Application.Sheets("WCPAT").Activate
Columns("A:B").Select
Range("A5").Activate
Selection.EntireColumn.Hidden = False
Range("B6:B65536").Select
End If
End Sub



It unhides the columns however it unhides all of them not just column B.
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Sort by date Sort by votes
Try

Code: 
Private Sub CheckBox1_Click()
Sheets("WCPAT").Columns("B").Hidden = checkbox1.Value = False
End Sub
 
Upvote 0
Awesome! Thank you so much. I wish I knew VB more in depth!!!
I have another question. I want to do the same thing but with rows. For example. Say I have a check box and when the check box is chosen I want to show rows 8 through 14. Can that be done as well?
 
Upvote 0
Try like this

Code: 
Private Sub CheckBox1_Click()
Sheets("WCPAT").Rows("8:14").Hidden = checkbox1.Value = False
End Sub
 
Upvote 0
One quick word of advice (I just went through this). With that above code, if you insert or delete any rows it will still hide row 8 even though that may no longer contain the information you want to hide.

If you go through and highlight the information you want to hide and select Insert--> Name--> define and make up a name for that information (ie name1) then you can create a macro with:

Range("Name1").EntireRow.Hidden = checkbox1.value (may or may not need = False. With Excel 03, I do not need it)

Then no matter how many rows are hidden / deleted, it will still hide that specific information. Just my .02.
 
Last edited:
Upvote 0
Ok I am having some issues with the code provided. I am trying to add a bunch of lines of code together. So I have about 20 checkboxes on a form. 13 of them are to hide/unhide certain columns and 7 of then are to hide/unhide certain rows. I am trying to put them all together but I can not get them to function together. Any help would be appreciated.
right now I have some of the code commented out b/c I was trying different approaches to get it to work.

Option Explicit
Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
Sheets("WCPAT").Columns("B").Hidden = False
Else
Sheets("WCPAT").Columns("B").Hidden = True
End If
'Private Sub CheckBox2_Click()
If CheckBox2.Value = True Then
Sheets("WCPAT").Columns("C").Hidden = False
Else
Sheets("WCPAT").Columns("C").Hidden = True
End If
'Private Sub CheckBox3_Click()
If CheckBox3.Value = True Then
Sheets("WCPAT").Columns("F").Hidden = False
Else
Sheets("WCPAT").Columns("F").Hidden = False
End If
'Private Sub CheckBox4_Click()
If CheckBox4.Value = True Then
Sheets("WCPAT").Columns("J").Hidden = False
Else
Sheets("WCPAT").Columns("J").Hidden = True
End If
'Private Sub CheckBox5_Click()
If CheckBox5.Value = True Then
Sheets("WCPAT").Columns("G").Hidden = False
Else
Sheets("WCPAT").Columns("G").Hidden = True
End If
'Private Sub CheckBox6_Click()
If CheckBox6.Value = True Then
Sheets("WCPAT").Columns("K").Hidden = False
Else
Sheets("WCPAT").Columns("K").Hidden = True
End If
'Private Sub CheckBox7_Click()
If CheckBox7.Value = True Then
Sheets("WCPAT").Columns("N").Hidden = False
Else
Sheets("WCPAT").Columns("N").Hidden = True
End If
'Private Sub CheckBox8_Click()
If CheckBox8.Value = True Then
Sheets("WCPAT").Columns("D").Hidden = False
Else
Sheets("WCPAT").Columns("D").Hidden = True
End If
'Private Sub CheckBox9_Click()
If CheckBox9.Value = True Then
Sheets("WCPAT").Columns("H").Hidden = False
Else
Sheets("WCPAT").Columns("H").Hidden = True
End If
'Private Sub CheckBox10_Click()
If CheckBox10.Value = True Then
Sheets("WCPAT").Columns("E").Hidden = False
Else
Sheets("WCPAT").Columns("E").Hidden = True
End If
'Private Sub CheckBox11_Click()
If CheckBox11.Value = True Then
Sheets("WCPAT").Columns("I").Hidden = False
Else
Sheets("WCPAT").Columns("I").Hidden = True
End If
'Private Sub CheckBox12_Click()
If CheckBox12.Value = True Then
Sheets("WCPAT").Columns("M").Hidden = False
Else
Sheets("WCPAT").Columns("M").Hidden = True
End If
'Private Sub CheckBox13_Click()
If CheckBox13.Value = True Then
Sheets("WCPAT").Columns("L").Hidden = False
Else
Sheets("WCPAT").Columns("L").Hidden = True
End If
'Private Sub CheckBox14_Click()
If CheckBox14.Value = True Then
Sheets("WCPAT").Rows("8:14").Hidden = False
Else
Sheets("WCPAT").Rows("8:14").Hidden = True
End If
'Private Sub CheckBox15_Click()
If CheckBox15.Value = True Then
Sheets("WCPAT").Rows("15:17").Hidden = False
Else
Sheets("WCPAT").Rows("15:17").Hidden = True
End If
'Private Sub CheckBox16_Click()
If CheckBox16.Value = True Then
Sheets("WCPAT").Rows("18:27").Hidden = False
Else
Sheets("WCPAT").Rows("18:27").Hidden = True
End If
'Private Sub CheckBox17_Click()
If CheckBox17.Value = True Then
Sheets("WCPAT").Rows("28:37").Hidden = False
Else
Sheets("WCPAT").Rows("28:37").Hidden = True
End If
'Private Sub CheckBox18_Click()
If CheckBox18.Value = True Then
Sheets("WCPAT").Rows("38:43").Hidden = False
Else
Sheets("WCPAT").Rows("38:43").Hidden = True
End If
'Private Sub CheckBox19_Click()
If CheckBox19.Value = True Then
Sheets("WCPAT").Rows("44:52").Hidden = False
Else
Sheets("WCPAT").Rows("44:52").Hidden = True
End If
'Private Sub CheckBox20_Click()
If CheckBox20.Value = True Then
Sheets("WCPAT").Rows("53:58").Hidden = False
Else
Sheets("WCPAT").Rows("53:58").Hidden = True
End If
End Sub
 
Upvote 0
I'd have to put an end sub after each Private Sub.... if I uncommented them. Then they seem to work independently. So if I check one then go back and uncheck it it stays unhidden.
 
Upvote 0
Try

Code: 
Private Sub CheckBox1_Click()
Call HideUnhide
End Sub
Private Sub CheckBox2_Click()
Call HideUnhide
End Sub
Private Sub CheckBox3_Click()
Call HideUnhide
End Sub
Private Sub CheckBox4_Click()
Call HideUnhide
End Sub
Private Sub CheckBox5_Click()
Call HideUnhide
End Sub
Private Sub CheckBox6_Click()
Call HideUnhide
End Sub
Private Sub CheckBox7_Click()
Call HideUnhide
End Sub
Private Sub CheckBox8_Click()
Call HideUnhide
End Sub
Private Sub CheckBox9_Click()
Call HideUnhide
End Sub
Private Sub CheckBox10_Click()
Call HideUnhide
End Sub
Private Sub CheckBox11_Click()
Call HideUnhide
End Sub
Private Sub CheckBox12_Click()
Call HideUnhide
End Sub
Private Sub CheckBox13_Click()
Call HideUnhide
End Sub
Private Sub CheckBox14_Click()
Call HideUnhide
End Sub
Private Sub CheckBox15_Click()
Call HideUnhide
End Sub
Private Sub CheckBox16_Click()
Call HideUnhide
End Sub
Private Sub CheckBox17_Click()
Call HideUnhide
End Sub
Private Sub CheckBox18_Click()
Call HideUnhide
End Sub
Private Sub CheckBox19_Click()
Call HideUnhide
End Sub
Private Sub CheckBox20_Click()
Call HideUnhide
End Sub


Private Sub HideUnhide()
If CheckBox1.Value = True Then
Sheets("WCPAT").Columns("B").Hidden = False
Else
Sheets("WCPAT").Columns("B").Hidden = True
End If
If CheckBox2.Value = True Then
Sheets("WCPAT").Columns("C").Hidden = False
Else
Sheets("WCPAT").Columns("C").Hidden = True
End If
If CheckBox3.Value = True Then
Sheets("WCPAT").Columns("F").Hidden = False
Else
Sheets("WCPAT").Columns("F").Hidden = False
End If
If CheckBox4.Value = True Then
Sheets("WCPAT").Columns("J").Hidden = False
Else
Sheets("WCPAT").Columns("J").Hidden = True
End If
If CheckBox5.Value = True Then
Sheets("WCPAT").Columns("G").Hidden = False
Else
Sheets("WCPAT").Columns("G").Hidden = True
End If
If CheckBox6.Value = True Then
Sheets("WCPAT").Columns("K").Hidden = False
Else
Sheets("WCPAT").Columns("K").Hidden = True
End If
If CheckBox7.Value = True Then
Sheets("WCPAT").Columns("N").Hidden = False
Else
Sheets("WCPAT").Columns("N").Hidden = True
End If
If CheckBox8.Value = True Then
Sheets("WCPAT").Columns("D").Hidden = False
Else
Sheets("WCPAT").Columns("D").Hidden = True
End If
If CheckBox9.Value = True Then
Sheets("WCPAT").Columns("H").Hidden = False
Else
Sheets("WCPAT").Columns("H").Hidden = True
End If
If CheckBox10.Value = True Then
Sheets("WCPAT").Columns("E").Hidden = False
Else
Sheets("WCPAT").Columns("E").Hidden = True
End If
If CheckBox11.Value = True Then
Sheets("WCPAT").Columns("I").Hidden = False
Else
Sheets("WCPAT").Columns("I").Hidden = True
End If
If CheckBox12.Value = True Then
Sheets("WCPAT").Columns("M").Hidden = False
Else
Sheets("WCPAT").Columns("M").Hidden = True
End If
If CheckBox13.Value = True Then
Sheets("WCPAT").Columns("L").Hidden = False
Else
Sheets("WCPAT").Columns("L").Hidden = True
End If
If CheckBox14.Value = True Then
Sheets("WCPAT").Rows("8:14").Hidden = False
Else
Sheets("WCPAT").Rows("8:14").Hidden = True
End If
If CheckBox15.Value = True Then
Sheets("WCPAT").Rows("15:17").Hidden = False
Else
Sheets("WCPAT").Rows("15:17").Hidden = True
End If
If CheckBox16.Value = True Then
Sheets("WCPAT").Rows("18:27").Hidden = False
Else
Sheets("WCPAT").Rows("18:27").Hidden = True
End If
If CheckBox17.Value = True Then
Sheets("WCPAT").Rows("28:37").Hidden = False
Else
Sheets("WCPAT").Rows("28:37").Hidden = True
End If
If CheckBox18.Value = True Then
Sheets("WCPAT").Rows("38:43").Hidden = False
Else
Sheets("WCPAT").Rows("38:43").Hidden = True
End If
If CheckBox19.Value = True Then
Sheets("WCPAT").Rows("44:52").Hidden = False
Else
Sheets("WCPAT").Rows("44:52").Hidden = True
End If
If CheckBox20.Value = True Then
Sheets("WCPAT").Rows("53:58").Hidden = False
Else
Sheets("WCPAT").Rows("53:58").Hidden = True
End If
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
VBA Hide/Unhide Columns, different/multiple sheets
2 3
Replies
20
Views
512
Minx_Minxy
M
edge37
  • Solved
Toggle hide/unhide columns without affecting tables or text boxes on top
Replies
4
Views
220
Peter_SSs
Peter_SSs
S
  • Solved
VBA code to use checkboxes to hide sheets.
2
Replies
14
Views
975
Joe4
Joe4
D
  • Question Question
Multiple Monitors - tab selection
Replies
5
Views
293
Candyman8019
C
C
  • Solved
Hide Columns
Replies
9
Views
165
Joe4
Joe4

Forum statistics

Threads
1,224,516
Messages
6,179,231
Members
452,898
Latest member
Capolavoro009

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