Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding cells in another worksheet
Dear all
I have the following simple macro in a worksheet object to hide certain columns whenever a change is made to a worksheet. Private Sub Worksheet_Change(ByVal Target As Range) Dim cell As Range For Each cell In Range("dDataReq1") Columns(cell.Column).Hidden = False If cell.Value = 0 Then Columns(cell.Column).Hidden = True End If Next cell End Sub The problem I have is that when the range "dDataReq1" is in another worksheet, and I want the columns in that worksheet to be hidden, I get the following error: method "Range" of object "_worksheet" failed. It is obvious that my method of referencing the range "dDataReq1" is ineffective when "dDataReq1" is in a different worksheet, but I can't seem to find a way around it. Can anybody help with this? Thanks JT |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding cells in another worksheet
One way:
Private Sub Worksheet_Change(ByVal Target As Range) Dim rCell As Range For Each rCell In Me.Parent.Names("dDataReq1").RefersToRange rCell.EntireColumn.Hidden = (rCell.Value = 0) Next rCell End Sub In article , JT wrote: Dear all I have the following simple macro in a worksheet object to hide certain columns whenever a change is made to a worksheet. Private Sub Worksheet_Change(ByVal Target As Range) Dim cell As Range For Each cell In Range("dDataReq1") Columns(cell.Column).Hidden = False If cell.Value = 0 Then Columns(cell.Column).Hidden = True End If Next cell End Sub The problem I have is that when the range "dDataReq1" is in another worksheet, and I want the columns in that worksheet to be hidden, I get the following error: method "Range" of object "_worksheet" failed. It is obvious that my method of referencing the range "dDataReq1" is ineffective when "dDataReq1" is in a different worksheet, but I can't seem to find a way around it. Can anybody help with this? Thanks JT |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding cells in another worksheet
Thanks, works a treat.
Thanks for also tidying up the rest of the code. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hiding a Worksheet | Excel Discussion (Misc queries) | |||
Hiding a Worksheet | Excel Programming | |||
Hiding a Worksheet | Excel Programming | |||
Hiding a Worksheet | Excel Programming | |||
Hiding a Worksheet | Excel Programming |