| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
unhide rows based on data validation
I have a data validation list in cell $E$30 of either "0" or "1".
If the user selects "0" I want to hide rows 144 - 238. If the user selects "1" I want rows 144-238 to appear. I tried the following code - but it didn't seem to work. Any help is appreciated. Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False If Range("E30").Value = "0" Then Range(Rows(144), Rows(238)).EntireRow.Hidden = True ElseIf Range("E30").Value = "1" Then Range(Rows(144), Rows(238)).EntireRow.Hidden = False: End If Application.ScreenUpdating = True End Sub -- Cathy |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
unhide rows based on data validation
Give this a try...
Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False If Range("E30").Value = "0" Then Rows("144:238").EntireRow.Hidden = True ElseIf Range("E30").Value = "1" Then Rows("144:238").EntireRow.Hidden = False End If Application.ScreenUpdating = True End Sub Rick "cathy" wrote in message ... I have a data validation list in cell $E$30 of either "0" or "1". If the user selects "0" I want to hide rows 144 - 238. If the user selects "1" I want rows 144-238 to appear. I tried the following code - but it didn't seem to work. Any help is appreciated. Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False If Range("E30").Value = "0" Then Range(Rows(144), Rows(238)).EntireRow.Hidden = True ElseIf Range("E30").Value = "1" Then Range(Rows(144), Rows(238)).EntireRow.Hidden = False: End If Application.ScreenUpdating = True End Sub -- Cathy |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
unhide rows based on data validation
On 5 Jun., 20:20, cathy wrote:
I have a data validation list in cell $E$30 of either "0" or "1". If the user selects "0" I want to hide rows 144 - 238. If the user selects "1" I want rows 144-238 to appear. I tried the following code - but it didn't seem to work. *Any help is appreciated. Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False If Range("E30").Value = "0" Then * *Range(Rows(144), Rows(238)).EntireRow.Hidden = True ElseIf Range("E30").Value = "1" Then * *Range(Rows(144), Rows(238)).EntireRow.Hidden = False: End If Application.ScreenUpdating = True End Sub -- Cathy Hi Cathy Try this: Private Sub Worksheet_Change(ByVal Target As Range) Set isect = Intersect(Target, Range("E30")) If Not isect Is Nothing Then If Target.Value = 0 Then Range("A144:A238").EntireRow.Hidden = True Else Range("A144:A238").EntireRow.Hidden = False End If End If End Sub Regards, Per |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
unhide rows based on data validation
Actually, it looks like the EntireRow qualifier is not required with a call
to Rows... this seems to work fine... Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False If Range("E30").Value = "0" Then Rows("144:238").Hidden = True ElseIf Range("E30").Value = "1" Then Rows("144:238").Hidden = False End If Application.ScreenUpdating = True End Sub Rick "Rick Rothstein (MVP - VB)" wrote in message ... Give this a try... Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False If Range("E30").Value = "0" Then Rows("144:238").EntireRow.Hidden = True ElseIf Range("E30").Value = "1" Then Rows("144:238").EntireRow.Hidden = False End If Application.ScreenUpdating = True End Sub Rick "cathy" wrote in message ... I have a data validation list in cell $E$30 of either "0" or "1". If the user selects "0" I want to hide rows 144 - 238. If the user selects "1" I want rows 144-238 to appear. I tried the following code - but it didn't seem to work. Any help is appreciated. Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False If Range("E30").Value = "0" Then Range(Rows(144), Rows(238)).EntireRow.Hidden = True ElseIf Range("E30").Value = "1" Then Range(Rows(144), Rows(238)).EntireRow.Hidden = False: End If Application.ScreenUpdating = True End Sub -- Cathy |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
unhide rows based on data validation
PERFECT!! Thank you VERY much!!!
-- Cathy "Rick Rothstein (MVP - VB)" wrote: Actually, it looks like the EntireRow qualifier is not required with a call to Rows... this seems to work fine... Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False If Range("E30").Value = "0" Then Rows("144:238").Hidden = True ElseIf Range("E30").Value = "1" Then Rows("144:238").Hidden = False End If Application.ScreenUpdating = True End Sub Rick "Rick Rothstein (MVP - VB)" wrote in message ... Give this a try... Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False If Range("E30").Value = "0" Then Rows("144:238").EntireRow.Hidden = True ElseIf Range("E30").Value = "1" Then Rows("144:238").EntireRow.Hidden = False End If Application.ScreenUpdating = True End Sub Rick "cathy" wrote in message ... I have a data validation list in cell $E$30 of either "0" or "1". If the user selects "0" I want to hide rows 144 - 238. If the user selects "1" I want rows 144-238 to appear. I tried the following code - but it didn't seem to work. Any help is appreciated. Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False If Range("E30").Value = "0" Then Range(Rows(144), Rows(238)).EntireRow.Hidden = True ElseIf Range("E30").Value = "1" Then Range(Rows(144), Rows(238)).EntireRow.Hidden = False: End If Application.ScreenUpdating = True End Sub -- Cathy |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
unhide rows based on data validation
Actually, Per include a filter which I inadvertently left out. Combining
such a filter into the code I previously posted yields this code which you should use instead of what I posted earlier... Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$E$30" Then Application.ScreenUpdating = False If Range("E30").Value = "0" Then Rows("144:238").Hidden = True ElseIf Range("E30").Value = "1" Then Rows("144:238").Hidden = False End If Application.ScreenUpdating = True End If End Sub The filter on the Target (the cell you are changing) stops the code from executing when something else other than E30 is changed. By the way, what did you want to have happen if E30 is set to something other than 0 or 1? Or are those possible values enforced in code somehow? Right now, if you set E30 to something else, nothing happens to affect the visibility of those rows. Rick "Rick Rothstein (MVP - VB)" wrote in message ... Actually, it looks like the EntireRow qualifier is not required with a call to Rows... this seems to work fine... Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False If Range("E30").Value = "0" Then Rows("144:238").Hidden = True ElseIf Range("E30").Value = "1" Then Rows("144:238").Hidden = False End If Application.ScreenUpdating = True End Sub Rick "Rick Rothstein (MVP - VB)" wrote in message ... Give this a try... Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False If Range("E30").Value = "0" Then Rows("144:238").EntireRow.Hidden = True ElseIf Range("E30").Value = "1" Then Rows("144:238").EntireRow.Hidden = False End If Application.ScreenUpdating = True End Sub Rick "cathy" wrote in message ... I have a data validation list in cell $E$30 of either "0" or "1". If the user selects "0" I want to hide rows 144 - 238. If the user selects "1" I want rows 144-238 to appear. I tried the following code - but it didn't seem to work. Any help is appreciated. Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False If Range("E30").Value = "0" Then Range(Rows(144), Rows(238)).EntireRow.Hidden = True ElseIf Range("E30").Value = "1" Then Range(Rows(144), Rows(238)).EntireRow.Hidden = False: End If Application.ScreenUpdating = True End Sub -- Cathy |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
unhide rows based on data validation
Now, I'm running into another problem. This worksheet is password protected.
When I password protect the VBA code will not work - is there any way to work around this? -- Cathy "Per Jessen" wrote: On 5 Jun., 20:20, cathy wrote: I have a data validation list in cell $E$30 of either "0" or "1". If the user selects "0" I want to hide rows 144 - 238. If the user selects "1" I want rows 144-238 to appear. I tried the following code - but it didn't seem to work. Any help is appreciated. Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False If Range("E30").Value = "0" Then Range(Rows(144), Rows(238)).EntireRow.Hidden = True ElseIf Range("E30").Value = "1" Then Range(Rows(144), Rows(238)).EntireRow.Hidden = False: End If Application.ScreenUpdating = True End Sub -- Cathy Hi Cathy Try this: Private Sub Worksheet_Change(ByVal Target As Range) Set isect = Intersect(Target, Range("E30")) If Not isect Is Nothing Then If Target.Value = 0 Then Range("A144:A238").EntireRow.Hidden = True Else Range("A144:A238").EntireRow.Hidden = False End If End If End Sub Regards, Per |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
unhide rows based on data validation
Look up the Protect and Unprotect methods in the help files (type Protect
into the Immediate window and hit F1; See Also on its help page can get you to the help page for Unprotect). By the way, make sure you see my newest posting against the suggested code you indicated you were going to use... I made a modification to it. Rick "cathy" wrote in message ... Now, I'm running into another problem. This worksheet is password protected. When I password protect the VBA code will not work - is there any way to work around this? -- Cathy "Per Jessen" wrote: On 5 Jun., 20:20, cathy wrote: I have a data validation list in cell $E$30 of either "0" or "1". If the user selects "0" I want to hide rows 144 - 238. If the user selects "1" I want rows 144-238 to appear. I tried the following code - but it didn't seem to work. Any help is appreciated. Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False If Range("E30").Value = "0" Then Range(Rows(144), Rows(238)).EntireRow.Hidden = True ElseIf Range("E30").Value = "1" Then Range(Rows(144), Rows(238)).EntireRow.Hidden = False: End If Application.ScreenUpdating = True End Sub -- Cathy Hi Cathy Try this: Private Sub Worksheet_Change(ByVal Target As Range) Set isect = Intersect(Target, Range("E30")) If Not isect Is Nothing Then If Target.Value = 0 Then Range("A144:A238").EntireRow.Hidden = True Else Range("A144:A238").EntireRow.Hidden = False End If End If End Sub Regards, Per |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| unhide row does not unhide the hidden rows | Excel Worksheet Functions | |||
| Hide/Show Rows based on Cell Value with Data Validation | Excel Programming | |||
| Data Validation based on data to left but several columns dependen | Excel Programming | |||
| Showing or Hiding rows based on data validation list | Excel Programming | |||
| Using VB to unhide hidden rows based on user response | Excel Programming | |||
Linear Mode
