| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Separating sub procedures
My VBA code hides certain rows in my spreadsheet - however I have created two
different ones - relying on two different Targets. It will run, but only pays attention to the last Target I have commanded. What have I done wrong? Please help as I am an absolute beginner at VBA. Please see code attached: Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Range("G25"), Target) Is Nothing Then Range("A28:A32").EntireRow.Hidden = True If Target.Value = "Yes" Then Range("A28:A32").EntireRow.Hidden = True Range("A33:A999").EntireRow.Hidden = False Else If Target.Value = "" Then Range("A28:A32").EntireRow.Hidden = True Range("A33:A999").EntireRow.Hidden = False Else Range("A28:A32").EntireRow.Hidden = False Range("A33:A999").EntireRow.Hidden = True End If End If End If If Not Application.Intersect(Range("E22"), Target) Is Nothing Then Range("A53:A59").EntireRow.Hidden = True If Target.Value = "No" Then Range("A53:A59").EntireRow.Hidden = True Else Range("A53:A59").EntireRow.Hidden = False End If End If End Sub |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Separating sub procedures
It works for me. What part of the code is not executing ?
Daniel My VBA code hides certain rows in my spreadsheet - however I have created two different ones - relying on two different Targets. It will run, but only pays attention to the last Target I have commanded. What have I done wrong? Please help as I am an absolute beginner at VBA. Please see code attached: Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Range("G25"), Target) Is Nothing Then Range("A28:A32").EntireRow.Hidden = True If Target.Value = "Yes" Then Range("A28:A32").EntireRow.Hidden = True Range("A33:A999").EntireRow.Hidden = False Else If Target.Value = "" Then Range("A28:A32").EntireRow.Hidden = True Range("A33:A999").EntireRow.Hidden = False Else Range("A28:A32").EntireRow.Hidden = False Range("A33:A999").EntireRow.Hidden = True End If End If End If If Not Application.Intersect(Range("E22"), Target) Is Nothing Then Range("A53:A59").EntireRow.Hidden = True If Target.Value = "No" Then Range("A53:A59").EntireRow.Hidden = True Else Range("A53:A59").EntireRow.Hidden = False End If End If End Sub |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Separating sub procedures
If in the first instance you say "No" in cell E22 so that rows 53 - 59 hide.
After this, say "Yes" to G25 in order to make sure that rows 28-32 hide and that 33-999 don't hide - you will see that the original command is undone - i.e. rows 53-59 are now unhidden again. The code only seems to respond to the last command you have affected. "Daniel.C" wrote: It works for me. What part of the code is not executing ? Daniel My VBA code hides certain rows in my spreadsheet - however I have created two different ones - relying on two different Targets. It will run, but only pays attention to the last Target I have commanded. What have I done wrong? Please help as I am an absolute beginner at VBA. Please see code attached: Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Range("G25"), Target) Is Nothing Then Range("A28:A32").EntireRow.Hidden = True If Target.Value = "Yes" Then Range("A28:A32").EntireRow.Hidden = True Range("A33:A999").EntireRow.Hidden = False Else If Target.Value = "" Then Range("A28:A32").EntireRow.Hidden = True Range("A33:A999").EntireRow.Hidden = False Else Range("A28:A32").EntireRow.Hidden = False Range("A33:A999").EntireRow.Hidden = True End If End If End If If Not Application.Intersect(Range("E22"), Target) Is Nothing Then Range("A53:A59").EntireRow.Hidden = True If Target.Value = "No" Then Range("A53:A59").EntireRow.Hidden = True Else Range("A53:A59").EntireRow.Hidden = False End If End If End Sub |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Separating sub procedures
That seems normal since 33-999 are unhidden, 53-59 are unhidden. You'll
have to unhide 33-52 and 60-999. Daniel If in the first instance you say "No" in cell E22 so that rows 53 - 59 hide. After this, say "Yes" to G25 in order to make sure that rows 28-32 hide and that 33-999 don't hide - you will see that the original command is undone - i.e. rows 53-59 are now unhidden again. The code only seems to respond to the last command you have affected. "Daniel.C" wrote: It works for me. What part of the code is not executing ? Daniel My VBA code hides certain rows in my spreadsheet - however I have created two different ones - relying on two different Targets. It will run, but only pays attention to the last Target I have commanded. What have I done wrong? Please help as I am an absolute beginner at VBA. Please see code attached: Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Range("G25"), Target) Is Nothing Then Range("A28:A32").EntireRow.Hidden = True If Target.Value = "Yes" Then Range("A28:A32").EntireRow.Hidden = True Range("A33:A999").EntireRow.Hidden = False Else If Target.Value = "" Then Range("A28:A32").EntireRow.Hidden = True Range("A33:A999").EntireRow.Hidden = False Else Range("A28:A32").EntireRow.Hidden = False Range("A33:A999").EntireRow.Hidden = True End If End If End If If Not Application.Intersect(Range("E22"), Target) Is Nothing Then Range("A53:A59").EntireRow.Hidden = True If Target.Value = "No" Then Range("A53:A59").EntireRow.Hidden = True Else Range("A53:A59").EntireRow.Hidden = False End If End If End Sub |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Separating sub procedures
You're right - thanks
"Daniel.C" wrote: That seems normal since 33-999 are unhidden, 53-59 are unhidden. You'll have to unhide 33-52 and 60-999. Daniel If in the first instance you say "No" in cell E22 so that rows 53 - 59 hide. After this, say "Yes" to G25 in order to make sure that rows 28-32 hide and that 33-999 don't hide - you will see that the original command is undone - i.e. rows 53-59 are now unhidden again. The code only seems to respond to the last command you have affected. "Daniel.C" wrote: It works for me. What part of the code is not executing ? Daniel My VBA code hides certain rows in my spreadsheet - however I have created two different ones - relying on two different Targets. It will run, but only pays attention to the last Target I have commanded. What have I done wrong? Please help as I am an absolute beginner at VBA. Please see code attached: Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Range("G25"), Target) Is Nothing Then Range("A28:A32").EntireRow.Hidden = True If Target.Value = "Yes" Then Range("A28:A32").EntireRow.Hidden = True Range("A33:A999").EntireRow.Hidden = False Else If Target.Value = "" Then Range("A28:A32").EntireRow.Hidden = True Range("A33:A999").EntireRow.Hidden = False Else Range("A28:A32").EntireRow.Hidden = False Range("A33:A999").EntireRow.Hidden = True End If End If End If If Not Application.Intersect(Range("E22"), Target) Is Nothing Then Range("A53:A59").EntireRow.Hidden = True If Target.Value = "No" Then Range("A53:A59").EntireRow.Hidden = True Else Range("A53:A59").EntireRow.Hidden = False End If End If End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Pivotcharts and stored procedures | Charts and Charting in Excel | |||
| pivot table creation procedures | Excel Discussion (Misc queries) | |||
| VBA function Procedures.... | Excel Worksheet Functions | |||
| VBA functions and Procedures.... | Excel Worksheet Functions | |||
| Sharing VBA procedures; alternative to personal.xls? | Excel Discussion (Misc queries) | |||
Linear Mode
