Hide columns

Sharid

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
1,064
Office Version
  1. 2016
Platform
  1. Windows
I have 30 columns that the user could hide if they wished. Different users will need to look at different columns so the option to hide and unhide columns is needed.

I have written this code below to hide and unhide a column when a check box is checked on a user form. Ideally I would like it to work from a command button.

Is there a better way I could write this code to hide certain columns. Lets say columns A,C,D,F,G,I,M,P

Or will I have to write this for each column and checkbox

Private Sub CheckBox1_Click()
If CheckBox1.Value = False Then
Sheet1.Columns("A").EntireColumn.Hidden = True
Else
If CheckBox1.Value = True Then
Sheet1.Columns("A").EntireColumn.Hidden = False
End If
End If
End Sub
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
Try

Code: 
Private Sub CheckBox1_Click()
Sheet1.Range("A1,C1,D1,F1,G1,I1,M1,P1").EntireColumn.Hidden = CheckBox1.Value = False
End Sub
 
Upvote 0
Sorry VoG

May be I did not explain it as well. I am correct your method will HIDE all the columns when the check box is checked, am I correct in thinking this, as this is not what I want.

The code you and I wrote work when the check box is checked,

Firstly I want the code to only run when a command button is clicked

Secondly different users may want to hide different columns, therefore as there are 30 columns that could be hidden. I was going to have 30 check boxes that would need to be check and when the user clicked the command button the code would run and hide the selected column. i.e.

user 1 may only want to hide 4 columns,
user 2 may only want to hide 8 columns
user 3 may only want to hide 20 columns

does this make a bit more clear.

I did not want to write the code 30 time
 
Upvote 0
In that case I would suggest not using check boxes but using two command buttons, one to hide and one to unhide. The one for hiding lets the user select the columns to hide. The unhide one unhides all columns.

Code: 
Private Sub CommandButton1_Click()
Dim r As Range
On Error Resume Next
Set r = Application.InputBox("Select columns to hide: hold down CTRL to select multiple columns", Type:=8)
On Error GoTo 0
If r Is Nothing Then Exit Sub
r.EntireColumn.Hidden = True
End Sub

Private Sub CommandButton2_Click()
ActiveSheet.UsedRange.Columns.Hidden = False
End Sub
 
Upvote 0
thanks I think this will do the trick
 
Upvote 0
You must log in or register to reply here.

Similar threads

Y
  • Question
How to automatically hide/unhide columns based on related month with VBA
Replies
1
Views
222
ysfuyar
Y
L
  • Solved
Vba looping thru cells and unhiding column based on value
Replies
8
Views
336
Joe4
Joe4
H
  • Question
Strange CheckBox issue
Replies
2
Views
176
hazmat
H
I
  • Solved
Clear checkbox when workbook open advice
Replies
5
Views
164
smozgur
smozgur
I
  • Solved
Hide command button if cell has a value advice
Replies
8
Views
307
dmt32
D

Forum statistics

Threads
1,221,418
Messages
6,159,790
Members
451,589
Latest member
Harold14

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