Hide Co9lumns based on cell value

[garygoodguy]

Hi,

I have some vba to hide some columns in one sheet based on a cell valu
in another sheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Sheets("Start").Range("U4").Value = "OFF" Then
Columns("P:AB").EntireColumn.Hidden = True
Else
Columns("P:AB").EntireColumn.Hidden = False
End If
End Sub

As you can see I have placed this code in the Worksheet_SelectionChange
This works fine, however it looks for that cell value everytime the use
moves around the sheet. What I'm after is to only look at the value onc
when the workbook opens (as it will not change from then on).

I have tried placing the code in both Private Sub's Worksheet_Change
Worksheet_Calculate, but the existing code does not work in either o
these.

Any advice would be appreciated.

Cheers

 

[Claus Busch]

Hi Gary,

Am Mon, 21 Jan 2013 02:46:05 +0000 schrieb garygoodguy:

I have some vba to hide some columns in one sheet based on a cell value
in another sheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Sheets("Start").Range("U4").Value = "OFF" Then
Columns("P:AB").EntireColumn.Hidden = True
Else
Columns("P:AB").EntireColumn.Hidden = False
End If
End Sub

in which sheet (name) you want to hide the columns?


Regards
Claus Busch

 

[Ben McClave]

Gary,

Place this code in the ThisWorkbook module and change "MySheet" to the sheet you wish to hide columns on.

Ben


Private Sub Workbook_Open()

Sheets("MySheet").Columns("P:AB").EntireColumn.Hidden = _
(Sheets("Start").Range("U4").Value = "OFF")

End Sub

 

[Ben McClave]

Gary,

One other thought: you could enter the code on the "Start" sheet's code module under the Deactivate event. That way the users can change cell U4 of the "Start" sheet and see the change right away, but you won't have the event firing every time a cell is selected on other sheets. Here's the modified code:

Private Sub Worksheet_Deactivate()

Sheets("MySheet").Columns("P:AB").EntireColumn.Hidden = _
(Sheets("Start").Range("U4").Value = "OFF")

End Sub

 

[garygoodguy]

Thanks Ben.

But this code gave me a syntax error.

 

[garygoodguy]

Hi,

I have some vba to hide some columns in one sheet based on a cell valu
in another sheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Sheets("Start").Range("U4").Value = "OFF" Then
Columns("P:AB").EntireColumn.Hidden = True
Else
Columns("P:AB").EntireColumn.Hidden = False
End If
End Sub

As you can see I have placed this code in the Worksheet_SelectionChange
This works fine, however it looks for that cell value everytime the use
moves around the sheet. What I'm after is to only look at the value onc
when the workbook opens (as it will not change from then on).

I have tried placing the code in both Private Sub's Worksheet_Change
Worksheet_Calculate, but the existing code does not work in either o
these.

Any advice would be appreciated.

Cheers.

So I got this to work. Placed this code in workbook_open and just adde
each sheet as necessary:


If Sheets("Start").Range("U4").Value = "OFF" Then
Sheets("MySheet").Columns("P:AB").EntireColumn.Hidden = True
Else
Sheets("MySheet").Columns("P:AB").EntireColumn.Hidden = False
End If

Thanks for the help

 

[CellShocked]

So I got this to work. Placed this code in workbook_open and just added
each sheet as necessary:


If Sheets("Start").Range("U4").Value = "OFF" Then
Sheets("MySheet").Columns("P:AB").EntireColumn.Hidden = True
Else
Sheets("MySheet").Columns("P:AB").EntireColumn.Hidden = False
End If

Thanks for the help.

I assume this would also work with rows?

I can make a version of this for my leap year handling, instead of
giving the user a pair of hide unhide buttons.