Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide cells
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 -- Nigel Graham |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide cells
Sorry should have said 'hide certain work sheets' ........
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide cells
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 -- Regards, Tom Ogilvy "Nigel Graham" wrote in message ... 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 -- Nigel Graham |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide cells
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" wrote in message ... 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 -- Regards, Tom Ogilvy "Nigel Graham" wrote in message ... 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 -- Nigel Graham |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide cells
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" wrote in message ... 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" wrote in message ... 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 -- Regards, Tom Ogilvy "Nigel Graham" wrote in message ... 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 -- Nigel Graham |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide cells
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 "Tom Ogilvy" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 -- Regards, Tom Ogilvy "Nigel Graham" wrote in message ... 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 -- Nigel Graham |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide cells
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. -- Regards, tom Ogilvy Nigel Graham wrote in message ... 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 "Tom Ogilvy" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 -- Regards, Tom Ogilvy "Nigel Graham" wrote in message ... 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 -- Nigel Graham |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
hide cells | Excel Worksheet Functions | |||
Want to show/hide cells depending on other cells results | Excel Discussion (Misc queries) | |||
Hide #VALUE! from cells | Excel Discussion (Misc queries) | |||
Cannot hide cells | New Users to Excel | |||
Cannot hide cells | Excel Worksheet Functions |