Hide cells

  • Thread starter Thread starter Nigel Graham
  • Start date Start date
N

Nigel Graham

I am trying to get some simple VBA which will hide certain cells or unhide
dependent on the content of a specific cell: If Cell P2 does not = JI hide
sheets data1, data2, data3 and data4 Likewise if it does = JI unhide those
sheets.
I have done this in the past but can not find an old example to view. Any
help would be useful
 
You fail to say where the check of P2 against J1 (JI ?) is performed.

Is it done on each sheet and then the sheet is hidden or is the check made
on a single sheet and then all 4 sheets are either hidden or unhidden.

Dim sh as Worksheet
for each sh in Worksheets(Array("data1","data2","data3","data4"))
with sh
if .Range("P2").value <> .Range("J1").Value then
.visible = xlSheetHidden
else
.Visible = xlSheetVisible
end if
End With
Next

or the second interpretation.

Dim lFlag as Long
Dim sh as Worksheet
With Worksheets("Summary")
if .Range("P2").value <> .Range("J1").Value then
lFlag = xlSheetHidden
Else
lFlag = xlSheetVisible
End if
End With
for each sh in Worksheets(Array("data1","data2","data3","data4"))
sh.Visible = lFlag
Next
 
Sorry I need to monitor Cell P2 to check if it says JI in the cell. Cell P2
has a dropdown menu to select various options but only if JI is selected
will all the sheets be pertinent. P2 on the Cover sheet is used to populate
data all over the sheet but data1 - data4 are not pertinent if JI is
selected on the Cover sheet. Users can change the selection P2 whilst using
the workbook to view data from a total of 34 different areas. so they could
view the data with JI populated in P2 then come back and change the
selection later to view different data throughout the rest of the sheet. So
what I need is some VBA to monitor Cover!P2 constantly or at least on view
and on leaving Cover.


--
Nigel Graham
Hutton IT
www.hit.uk.net
Tom Ogilvy said:
You fail to say where the check of P2 against J1 (JI ?) is performed.

Is it done on each sheet and then the sheet is hidden or is the check made
on a single sheet and then all 4 sheets are either hidden or unhidden.

Dim sh as Worksheet
for each sh in Worksheets(Array("data1","data2","data3","data4"))
with sh
if .Range("P2").value <> .Range("J1").Value then
.visible = xlSheetHidden
else
.Visible = xlSheetVisible
end if
End With
Next

or the second interpretation.

Dim lFlag as Long
Dim sh as Worksheet
With Worksheets("Summary")
if .Range("P2").value <> .Range("J1").Value then
lFlag = xlSheetHidden
Else
lFlag = xlSheetVisible
End if
End With
for each sh in Worksheets(Array("data1","data2","data3","data4"))
sh.Visible = lFlag
Next
Click to expand...
 
If using xl2000 or later, you can use the worksheet change event for the
sheet cover. Right click on the sheet tab of cover and select view code.
At the top of the resulting module, In the left dropdown select Worksheet
and in the right dropdown select Change (not selectionchange)

I assume if P2 is JI hide sheets, otherwise unhide. If it is the reverse,
then change the <> in the if statement to =


Private Sub Worksheet_Change(ByVal Target As Range)
Dim lFlag as Long
If target.count > 1 then exit sub
if Target.Address = "$P$2" then
if me.Range("P2").value <> "JI" then
lFlag = xlSheetVisible
Else
lFlag = xlSheetHidden
End if

for each sh in Worksheets(Array("data1","data2","data3","data4"))
sh.Visible = lFlag
Next
End if
End Sub



--
Regards,
Tom Ogilvy

Nigel Graham said:
Sorry I need to monitor Cell P2 to check if it says JI in the cell. Cell P2
has a dropdown menu to select various options but only if JI is selected
will all the sheets be pertinent. P2 on the Cover sheet is used to populate
data all over the sheet but data1 - data4 are not pertinent if JI is
selected on the Cover sheet. Users can change the selection P2 whilst using
the workbook to view data from a total of 34 different areas. so they could
view the data with JI populated in P2 then come back and change the
selection later to view different data throughout the rest of the sheet. So
what I need is some VBA to monitor Cover!P2 constantly or at least on view
and on leaving Cover.
Click to expand...
 
Thanks Tom
Exactly what I needed but had to add in
Sheets("Sum").Select
Range("P2").Select
as it appeared to upset the display when executed and didn't return to Cover
sheet when done. I assume it was because it still had one of the hidden
sheets on display when hidden?
Nigel Graham
 
What you added doesn't make any sense to me and if the lines were one after
the other, should raise an error since "Sum" is the activesheet and
Range("P2").Select would say to select P2 on worksheet "Cover" (assuming the
code is in the sheet module of sheet cover).

No selecting should be required and no changes in the selection would be
caused by the code I posted, acting on its own. Perhaps you have other code
that is being triggered. I tested it and as I said, no change in selection.
Only visible change was in the visible sheet tabs which would be expected.
 
Back
Top