Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
conditional row hide
I have the following:
Private Sub Worksheet_Change(ByVal target As Range) Application.ScreenUpdating = False 'Conditions & Ranges' Dim rng As Range, rng2 As Range, rng3 As Range Dim wf As WorksheetFunction Dim cond1 As Boolean, cond2 As Boolean, cond3 As Boolean, cond4 As Boolean Set wf = Application.WorksheetFunction Set rng = Me.Range("E31") 'Option 2' Set rng2 = Me.Range("B7") 'Packing Type' Set rng3 = Me.Range("D2") 'Job Type cond1 = (UCase(rng3.Value) = "Corporate") cond2 = (UCase(rng3.Value) = "Private") cond3 = (UCase(rng2.Value) = "PBR") cond4 = (UCase(rng2.Value) = "PBO") 'Hides Packing Prices' [37:37, 49:49].EntireRow.Hidden = wf.And(cond2, cond4) [36:36, 48:48].EntireRow.Hidden = wf.Or(wf.And(cond1, cond3), wf.And(cond2, cond4)) 'Hides depending on Type' [8:8, 41:43].EntireRow.Hidden = (rng3.Value = "Private") [9:20, 34:34, 38:40, 46:46].EntireRow.Hidden = (rng3.Value = "Corporate") 'Hides Option 2' If Not Intersect(rng, target) Is Nothing Then [35:35, 47:47].EntireRow.Hidden = IsEmpty(rng.Value) End If Application.ScreenUpdating = True End Sub All the hiding parts work, except for the first block "Hide Packing Prices", no rows are hiding!? What I am trying to do is have row 36 and row 48 hide when D2 = "Corporate" & B7 = "PBR" and also hide those 2 rows again when D2 = "Private" & B7 = "PBO" and hide row 37 and row 49 as well with the second one. What am I doing wrong? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
conditional row hide
ivory_kitten,
Ucase converts all letters in a string to upper case, so Ucase of no string will ever be "Corporate." In the immediate window: ? UCase("Corporate") CORPORATE hth, Doug "ivory_kitten" wrote in message ... I have the following: Private Sub Worksheet_Change(ByVal target As Range) Application.ScreenUpdating = False 'Conditions & Ranges' Dim rng As Range, rng2 As Range, rng3 As Range Dim wf As WorksheetFunction Dim cond1 As Boolean, cond2 As Boolean, cond3 As Boolean, cond4 As Boolean Set wf = Application.WorksheetFunction Set rng = Me.Range("E31") 'Option 2' Set rng2 = Me.Range("B7") 'Packing Type' Set rng3 = Me.Range("D2") 'Job Type cond1 = (UCase(rng3.Value) = "Corporate") cond2 = (UCase(rng3.Value) = "Private") cond3 = (UCase(rng2.Value) = "PBR") cond4 = (UCase(rng2.Value) = "PBO") 'Hides Packing Prices' [37:37, 49:49].EntireRow.Hidden = wf.And(cond2, cond4) [36:36, 48:48].EntireRow.Hidden = wf.Or(wf.And(cond1, cond3), wf.And(cond2, cond4)) 'Hides depending on Type' [8:8, 41:43].EntireRow.Hidden = (rng3.Value = "Private") [9:20, 34:34, 38:40, 46:46].EntireRow.Hidden = (rng3.Value = "Corporate") 'Hides Option 2' If Not Intersect(rng, target) Is Nothing Then [35:35, 47:47].EntireRow.Hidden = IsEmpty(rng.Value) End If Application.ScreenUpdating = True End Sub All the hiding parts work, except for the first block "Hide Packing Prices", no rows are hiding!? What I am trying to do is have row 36 and row 48 hide when D2 = "Corporate" & B7 = "PBR" and also hide those 2 rows again when D2 = "Private" & B7 = "PBO" and hide row 37 and row 49 as well with the second one. What am I doing wrong? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
conditional row hide
OMG i feel so stupid! LOL thanks alot!
"Doug Glancy" wrote: ivory_kitten, Ucase converts all letters in a string to upper case, so Ucase of no string will ever be "Corporate." In the immediate window: ? UCase("Corporate") CORPORATE hth, Doug "ivory_kitten" wrote in message ... I have the following: Private Sub Worksheet_Change(ByVal target As Range) Application.ScreenUpdating = False 'Conditions & Ranges' Dim rng As Range, rng2 As Range, rng3 As Range Dim wf As WorksheetFunction Dim cond1 As Boolean, cond2 As Boolean, cond3 As Boolean, cond4 As Boolean Set wf = Application.WorksheetFunction Set rng = Me.Range("E31") 'Option 2' Set rng2 = Me.Range("B7") 'Packing Type' Set rng3 = Me.Range("D2") 'Job Type cond1 = (UCase(rng3.Value) = "Corporate") cond2 = (UCase(rng3.Value) = "Private") cond3 = (UCase(rng2.Value) = "PBR") cond4 = (UCase(rng2.Value) = "PBO") 'Hides Packing Prices' [37:37, 49:49].EntireRow.Hidden = wf.And(cond2, cond4) [36:36, 48:48].EntireRow.Hidden = wf.Or(wf.And(cond1, cond3), wf.And(cond2, cond4)) 'Hides depending on Type' [8:8, 41:43].EntireRow.Hidden = (rng3.Value = "Private") [9:20, 34:34, 38:40, 46:46].EntireRow.Hidden = (rng3.Value = "Corporate") 'Hides Option 2' If Not Intersect(rng, target) Is Nothing Then [35:35, 47:47].EntireRow.Hidden = IsEmpty(rng.Value) End If Application.ScreenUpdating = True End Sub All the hiding parts work, except for the first block "Hide Packing Prices", no rows are hiding!? What I am trying to do is have row 36 and row 48 hide when D2 = "Corporate" & B7 = "PBR" and also hide those 2 rows again when D2 = "Private" & B7 = "PBO" and hide row 37 and row 49 as well with the second one. What am I doing wrong? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Hide | Excel Worksheet Functions | |||
Using VBA to Conditional Hide Rows? | Excel Discussion (Misc queries) | |||
Hide cell value conditional on its value | Excel Worksheet Functions | |||
Conditional Hide rows with zero | Excel Programming | |||
Conditional Column Hide | Excel Programming |