macro to hide columns and/or worksheets
 

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!!!!!!!!

macro to hide columns and/or worksheets
 

Try this

Sub MyHide()
If Range("A1").Value = Empty Then
Worksheets("Sheet2").Columns("F:K").EntireColumn.H idden = True
Worksheets("Sheet3").Columns("G:K").EntireColumn.H idden = 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

Ken K wrote:
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!!!!!!!!

macro to hide columns and/or worksheets
 

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" <Ken wrote in message
...
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!!!!!!!!

macro to hide columns and/or worksheets
 

Not sure how to do that....

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

"John" wrote:

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" <Ken wrote in message
...
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!!!!!!!!

macro to hide columns and/or worksheets
 

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

"Ken K" wrote in message
...
Not sure how to do that....

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

"John" wrote:

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" <Ken wrote in message
...
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!!!!!!!!