Showing/Hiding Columns by clicking a button

  • Thread starter Thread starter Brian
  • Start date Start date
B

Brian

Hi,

I would like to write some smarter code to -
(a) initially hide columns C-I on Worksheets 1-3, then
(b) show only the columns selected by check boxes on each of those
Worksheets after pressing a button named 'SIB'.

What I am doing at the moment for is the following:

Private Sub SIB_Click()
Worksheets("Sheet2").Columns("A:A").Hidden = False
Worksheets("Sheet2").Columns("B:B").Hidden = False
Worksheets("Sheet3").Columns("A:A").Hidden = False
Worksheets("Sheet3").Columns("B:B").Hidden = False
Worksheets("Sheet4").Columns("A:A").Hidden = False
Worksheets("Sheet4").Columns("B:B").Hidden = False

If CheckBox1.Value = True Then
Worksheets("Sheet2").Columns("C:C").Hidden = False
Worksheets("Sheet3").Columns("C:C").Hidden = False
Worksheets("Sheet4").Columns("C:C").Hidden = False
Else: Worksheets("Sheet2").Columns("C:C").Hidden = True
Worksheets("Sheet3").Columns("C:C").Hidden = True
Worksheets("Sheet4").Columns("C:C").Hidden = True

End If

If CheckBox2.Value = True Then
Worksheets("Sheet2").Columns("D:D").Hidden = False
Worksheets("Sheet3").Columns("D:D").Hidden = False
Worksheets("Sheet4").Columns("D:D").Hidden = False
Else: Worksheets("Sheet2").Columns("D:D").Hidden = True
Worksheets("Sheet3").Columns("D:D").Hidden = True
Worksheets("Sheet4").Columns("D:D").Hidden = True


End If

etc., etc. right through to CheckBox 7 & Column I.

I am not sure how to write efficient code to hide all the columns, then
write efficient code to hide all but the selected columns. If anyone can
help me on this I would be very grateful.

Best regards,
Brian
 
Private Sub SIB_Click()

Worksheets("Sheet2").Columns("C:C").Hidden = CheckBox1.Value
Worksheets("Sheet3").Columns("C:C").Hidden = CheckBox1.Value
Worksheets("Sheet4").Columns("C:C").Hidden = CheckBox1.Value

Worksheets("Sheet2").Columns("D:D").Hidden = CheckBox2.Value
Worksheets("Sheet3").Columns("D:D").Hidden = CheckBox2.Value
Worksheets("Sheet4").Columns("D:D").Hidden = CheckBox2.Value

'etc.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Bob Phillips said:
Private Sub SIB_Click()

Worksheets("Sheet2").Columns("C:C").Hidden = CheckBox1.Value
Worksheets("Sheet3").Columns("C:C").Hidden = CheckBox1.Value
Worksheets("Sheet4").Columns("C:C").Hidden = CheckBox1.Value

Worksheets("Sheet2").Columns("D:D").Hidden = CheckBox2.Value
Worksheets("Sheet3").Columns("D:D").Hidden = CheckBox2.Value
Worksheets("Sheet4").Columns("D:D").Hidden = CheckBox2.Value

'etc.
Click to expand...

That's fantastic and helps a lot, thank you very much Bob! Thanks for
responding so quickly, too.

Best regards,
Brian.
 
Bob Phillips said:
Private Sub SIB_Click()

Worksheets("Sheet2").Columns("C:C").Hidden = CheckBox1.Value
Worksheets("Sheet3").Columns("C:C").Hidden = CheckBox1.Value
Worksheets("Sheet4").Columns("C:C").Hidden = CheckBox1.Value

Worksheets("Sheet2").Columns("D:D").Hidden = CheckBox2.Value
Worksheets("Sheet3").Columns("D:D").Hidden = CheckBox2.Value
Worksheets("Sheet4").Columns("D:D").Hidden = CheckBox2.Value

'etc.
Click to expand...

Hi again Bob,

oops, I spoke a little too soon. Your code makes a checkbox hide a column
rather than show a column. I am not sure how to reverse the situation. I
tried using "Visible" instead of "Hidden", but that only gave an error
message telling me there is no such property for this type of Object. Any
ideas how I might get the checkboxes to show the columns?

Best regards,
Brian.
 
I wasn't sure about the setting, your words seem to be against the code you
gave <g>

Just logically reverse it

= Not CheckBox1.Value

etc.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Bob Phillips said:
I wasn't sure about the setting, your words seem to be against the code you
gave <g>

Just logically reverse it

= Not CheckBox1.Value

etc.
Click to expand...

Thanks again, Bob.

I apologise for the ambiguity in my original post. I am completely new to
VBA, I have a lot of reading to do. I didn't even know one could place a NOT
statement like that. I appreciate your help very much.

Best regards,
Brian.
 
No need to apologise, I read it as ambiguity and took a punt ... wrong
<vbg>. I should have trusted the code <ebg>

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Back
Top