Code to hide rows if cell in another WS is ="No"

[googley]

I have two worksheets Estimate Job Information and Proposal.
When I have a drop down menu yes /no cell in Estimate job Information F103.
When the cell is changed to "NO" Hide Rows 78:81 in worksheet Proposal.

I've tried:
IF Range ("'Estimate Job Information'!$F$103")="YES" Then
Rows ("'Proposal'!78:81").Select
Selection.EntireRow.Hidden=True
End if
End Sub

I pasted this in the Thisworkbook (General) Worksheet_Change Module

I get nothing!VBA newbie!

 

[Claus Busch]

Hi,

Am Wed, 26 Mar 2014 10:02:40 -0700 (PDT) schrieb googley:

I have two worksheets Estimate Job Information and Proposal.
When I have a drop down menu yes /no cell in Estimate job Information F103.
When the cell is changed to "NO" Hide Rows 78:81 in worksheet Proposal.

right click on tab of sheet "Estimate Job Information" => Show code and
then paste the following code in the code window. If you change the
value in F103 the rows will be hidden or unhidden:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$F$103" Or Target.Count > 1 Then Exit Sub

Sheets("Proposal").Rows("78:81").Hidden = Target = "Yes"
End Sub


Regards
Claus B.

 

[googley]

Claus Thank you for your reply but it does nothing. I have the workbook saved as a macro-enabled, anything else I could be missing?

 

[Claus Busch]

Hi,

Am Wed, 26 Mar 2014 10:39:29 -0700 (PDT) schrieb googley:

Claus Thank you for your reply but it does nothing. I have the workbook saved as a macro-enabled, anything else I could be missing?

I don't know what is wrong. Please have a look:
https://onedrive.live.com/?cid=9378...#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for the workbook "HideRows"
Right click and download the file because macros are disabled in
OneDrive.


Regards
Claus B.

 

[googley]

Claus yours works admirably!

I copied over to my worksheet and nothing. I don't know what I'm doing wrong??

 

[Claus Busch]

Hi,

Am Wed, 26 Mar 2014 12:45:20 -0700 (PDT) schrieb googley:

I copied over to my worksheet and nothing. I don't know what I'm doing wrong??

did you run a macro with
Application.DisableEvents = True
and the macro failed?
Then your events are still disabled.
Write a little macro in a standard module:

Sub Start()
Application.EnableEvents = True
End Sub

Run the macro and test the code again. If it doesn't work send your
workbook to my address.


Regards
Claus B.

 

[googley]

Thank you
That works great!

How do I add additional yes no cells and rows to hide?

 

[Claus Busch]

Hi Alan,

Am Wed, 26 Mar 2014 17:33:00 -0700 (PDT) schrieb googley:

How do I add additional yes no cells and rows to hide?

do it with Select Case
E.g.:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub

With Sheets("PROPOSAL")
Select Case Target.Address(0, 0)
Case "F103"
.Rows("78:81").Hidden = LCase(Target) = "yes"
Case "F93"
.Rows("53:67").Hidden = LCase(Target) = "yes"
End Select
End With
End Sub


Regards
Claus B.

 

[googley]

I see I had more than one instance of Addresses with multiple Rows to be hidden.

so Case"F79"
..Rows("62") & .Rows(115:122).Hidden = LCase(Target)="no"

 

[Claus Busch]

Hi Alan,

Am Thu, 27 Mar 2014 07:56:22 -0700 (PDT) schrieb googley:

I see I had more than one instance of Addresses with multiple Rows to be hidden.

so Case"F79"
.Rows("62") & .Rows(115:122).Hidden = LCase(Target)="no"

in the Select Case Statement you can add all your cases.
If you can't get it to work please post the addresses and the rows the
should be hidden per address.


Regards
Claus B.

 

[googley]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub

With Sheets("PROPOSAL")
Select Case Target.Address(0, 0)
Case "F15"
.Rows("53").Hidden = LCase(Target) = "no"
Case "F23"
.Rows("54").Hidden = LCase(Target) = "no"
Case "F31"
.Rows("55").Hidden = LCase(Target) = "no"
Case "F40"
.Rows("56").Hidden = LCase(Target) = "no"
Case "F46"
.Rows("57").Hidden = LCase(Target) = "no"
Case "F52"
.Rows("58").Hidden = LCase(Target) = "no"
Case "F58"
.Rows("59").Hidden = LCase(Target) = "no"
Case "F65"
.Rows("60").Hidden = LCase(Target) = "no"
Case "F71"
.Rows("61").Hidden = LCase(Target) = "no"
Case "F79"
.Rows("62").Hidden = LCase(Target) = "no"
Case "F86"
.Rows("63").Hidden = LCase(Target) = "no"
Case "F93"
.Rows("64").Hidden = LCase(Target) = "no"
Case "F103"
.Rows("65").Hidden = LCase(Target) = "no"
Case "F109"
.Rows("66").Hidden = LCase(Target) = "no"
Case "F115"
.Rows("67").Hidden = LCase(Target) = "no"
End Select
End With
End Sub

I need to also add the following:
Case "F15"
.Rows("77:83").Hidden = LCase(Target) = "no"
Case "F40"
.Rows("84:90").Hidden = LCase(Target) = "no"

Case "F58"
.Rows("91:97").Hidden = LCase(Target) = "no"

Case "F71"
.Rows("98:105").Hidden = LCase(Target) = "no"

Case "F86"
.Rows("106:114").Hidden = LCase(Target) = "no"

Case "F79"
.Rows("115:122").Hidden = LCase(Target) = "no"

 

[Claus Busch]

Hi Alan,

Am Thu, 27 Mar 2014 09:21:47 -0700 (PDT) schrieb googley:

I need to also add the following:
Case "F15"
.Rows("77:83").Hidden = LCase(Target) = "no"
Case "F40"
.Rows("84:90").Hidden = LCase(Target) = "no"

Case "F58"
.Rows("91:97").Hidden = LCase(Target) = "no"

Case "F71"
.Rows("98:105").Hidden = LCase(Target) = "no"

Case "F86"
.Rows("106:114").Hidden = LCase(Target) = "no"

Case "F79"
.Rows("115:122").Hidden = LCase(Target) = "no"

please look for your mails. I sent you the file.
One case you may only write once. If you write it twice or more only the
first occurence of this case works.
Add the additional rows to delete also in the expected case.


Regards
Claus B.