VBA: To know whether columns are hidden/displayed

[inddon]

Hello There,

I have a checkbox 'CheckBoxShowSummary' in a userform. When it is checked the columns C:J are displayed, else it is hidden (please find the code below).

When the form shows up, this is what I am looking for in the code to perform:

1. Check if the columns C:J are hidden, then the checkbox should have a value False. If columns C:J are displayed, then it should have the value True.

2. Instead of hard coding the columns (C:J), is there a smarter way to do like using via the Named Range.

Could you please advise on how the above can be achieved?

Private Sub CheckBoxShowSummary_Click()
  If CheckBoxShowSummary = True Then
       Sheets("Sheet1").Columns("C:J").EntireColumn.Hidden = False
  Else
       Sheets("Sheet1").Columns("C:J").EntireColumn.Hidden = True
  End If
End Sub

Thanks & regards
Don

 

[prasaddn]

Hi Don,

I guess you have answers in your code itself.

I would use this if on either worksheet change event:

if Sheets("Sheet1").Columns("C:J").EntireColumn.Hidden then
   CheckBoxShowSummary = True
else
   CheckBoxShowSummary = false
end if

Also some more changes to your current code:

Private Sub CheckBoxShowSummary_Click()
  If CheckBoxShowSummary Then
       Sheets("Sheet1").Columns("C:J").EntireColumn.Hidden = False
       CheckBoxShowSummary = False
  Else
       Sheets("Sheet1").Columns("C:J").EntireColumn.Hidden = True
       CheckBoxShowSummary = True
  End If
End Sub

note: you dont have to use "= True" in IF condition line of code, as the condition set is boolean type.

Hope this solves.

With regard to namedranges yes, you can definitely do, but is the range going to be changing C:J .. C:K etc or always just C:J?

Regards,
Prasad DN

 

[inddon]

Hi Don,

I guess you have answers in your code itself.

I would use this if on either worksheet change event:

if Sheets("Sheet1").Columns("C:J").EntireColumn.Hidden then
   CheckBoxShowSummary = True
else
   CheckBoxShowSummary = false
end if

Also some more changes to your current code:

Private Sub CheckBoxShowSummary_Click()
  If CheckBoxShowSummary Then
       Sheets("Sheet1").Columns("C:J").EntireColumn.Hidden = False
       CheckBoxShowSummary = False
  Else
       Sheets("Sheet1").Columns("C:J").EntireColumn.Hidden = True
       CheckBoxShowSummary = True
  End If
End Sub

note: you dont have to use "= True" in IF condition line of code, as the condition set is boolean type.

Hope this solves.

With regard to namedranges yes, you can definitely do, but is the range going to be changing C:J .. C:K etc or always just C:J?

Regards,
Prasad DN

Hello Prasad,

Thank you for your support.

I will add extra columns forehand so that this code will not be affected.
Is there a way to handle the code if they add more columns afterwards?

Regards
Don

 

[prasaddn]

Hi Don,

thank you liking the solution. hope it serves the purpose.

Dynamic NamedRange is the solution. Chandoo.org and contextures.com and many other such excel related sites have completed explanation on dynamic namedrange.

Regards,
Prasad DN