|
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 |
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 04:00 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com