Hide columns

Sharid

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
1,066
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
 
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
You must log in or register to reply here.

Similar threads

F
  • Solved
Dynamic Hide & Unhide column
Replies
3
Views
82
Kaper
Kaper
Y
  • Question Question
How to automatically hide/unhide columns based on related month with VBA
Replies
1
Views
447
ysfuyar
Y
S
  • Solved
VBA code to use checkboxes to hide sheets.
2
Replies
14
Views
1K
Joe4
Joe4
M
  • Solved
VBA Hide/Unhide Columns, different/multiple sheets
2 3
Replies
20
Views
975
Minx_Minxy
M
edge37
  • Solved
Toggle hide/unhide columns without affecting tables or text boxes on top
Replies
8
Views
552
edge37
edge37

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