Hi,

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

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 user moves around the sheet. What I'm after is to only look at the value once 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 of these.

Any advice would be appreciated.

Cheers.

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

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.Hid den = _
(Sheets("Start").Range("U4").Value = "OFF")

End Sub

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.Hid den = _
(Sheets("Start").Range("U4").Value = "OFF")

End Sub

Thanks Ben.

But this code gave me a syntax error.

Claus,

There are multiple sheets (140 odd). An example is "1. Forecast".

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.Hid den = True
Else
Sheets("MySheet").Columns("P:AB").EntireColumn.Hid den = False
End If

Thanks for the help.

On Tue, 22 Jan 2013 07:02:08 +0000, garygoodguy
wrote:



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.Hi dden = True
Else
Sheets("MySheet").Columns("P:AB").EntireColumn.Hi dden = 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.

Not an expert - but don't see why it wouldn't also work with rows.