macro to hide columns and/or worksheets

[Ken K]

I need some help with a macro issue....

I have a spreadsheet with multiple worksheets in it, say

"worksheet1"
"worksheet2"
"worksheet3"
"worksheet4"


I want to conditionally hide either columns or entire worksheets based on
the value of a cell in Worksheet1

For instance:

IF "Worksheet1.B17" is NULL
Then Hide columns F:K on Worksheet2
and
Hide Hide columns G:K on Worksheet3
and Hide Worksheet4

Any help would be greatly appreciated!!!!!!!!

 

[Sandy]

Try this

Sub MyHide()
If Range("A1").Value = Empty Then
Worksheets("Sheet2").Columns("F:K").EntireColumn.Hidden = True
Worksheets("Sheet3").Columns("G:K").EntireColumn.Hidden = True
Worksheets("Sheet4").Visible = False
Else
Worksheets("Sheet2").Columns.Hidden = False
Worksheets("Sheet3").Columns.Hidden = False
Worksheets("Sheet4").Visible = True
End If
End Sub


Sandy

 

[John]

Hi Ken,

Same as Sandy's reply really, but if you put this code under the worksheet 1
object (ie not a separate module) it will automatically fire every time the
cell changes:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$17" Then
If IsError(Target.Value) = True Then
If Target.Value = CVErr(xlErrNull) Then
With Application
.Worksheets("Sheet2").Columns("F:K").EntireColumn.Hidden
= True
.Worksheets("Sheet3").Columns("G:K").EntireColumn.Hidden
= True
.Worksheets("Sheet4").Visible = False
End With
End If
Else
With Application
.Worksheets("Sheet2").Columns("F:K").EntireColumn.Hidden =
False
.Worksheets("Sheet3").Columns("G:K").EntireColumn.Hidden =
False
.Worksheets("Sheet4").Visible = True
End With
End If
End If
End Sub

Hope that helps

Best regards

John

 

[Ken K]

Not sure how to do that....

I have the program in a text file....

 

[John]

Ken,

What do you mean by "text file"? I assume you are tyring to run the code
for the VBE (that opens with Alt+F11) . If this is the case, then you have
a choice of where you place code, either in a Module named something like
"Module1" in the Project Explorer (CTRL+R), or one in of the Excel Objects
named something like "Sheet1" or "ThisWorkbook", also in the Project
Explorer.

Because you are wanting to run code based on a change in cell B17 in
Worksheet 1, this needs to go in the "Sheet1" object.

Does this make sense?

Best regards

John