Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!!!!!!!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!!!!!!!! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!!!!!!!! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!!!!!!!! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!!!!!!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need a macro to hide certain columns | Excel Discussion (Misc queries) | |||
I set up a macro to hide/unhide columns. It hides more columns | Excel Programming | |||
Is there a way to hide worksheets and/or rows/columns based on information enter into a particular cell of range of cells? | New Users to Excel | |||
macro to hide rows across several worksheets fails | Excel Discussion (Misc queries) | |||
Macro to hide Columns | Excel Programming |