Hide rows based on output from formula

[mustang70]

Hi I have searched many forums and can't seem to find my answer to this problem

I am working a large quoting program and I need certain rows to automatically hide when I have a cell condition based on a number i.e. if P30=1 I want the row hidden and if P30=2 I want the rows to appear. The numbers can be changed to "true" or "false" if it would work better.
Currently this formula works but only if I got to the P30 cell and click and hit enter - the cell out put is from a differnt sheet and I don't know if that the problem. I need this formual to run all of the time as I make changes to the first sheet and this data is on sheet 4 - this data has a Macro which copys the info and pastes this in Word
I have also tried Autofill with no luck.
There are about 10 other differnt rows I need to hide based on formual outputs - Ideas?

Here is the current formula - only works if I go to the sheet and manauly type 1 or 2 and hit enter, does not work from a formula output

Private Sub Worksheet_Change(ByVal target As Range)
If Range("s30").Value = 1 Then
Rows("34:35").Select
Selection.EntireRow.Hidden = True
Range("s30").Select
ElseIf Range("s30").Value = 2 Then
Rows("34:35").Select
Selection.EntireRow.Hidden = False
Range("s30").Select
End If

End Sub

 

[mustang70]

That is correct - basically there are several different lines that have quote info and option button 2 does need to allow only line 33 to show, but I am going to stick with the original formula you provided for now. I just am not sure on how to add rows 92-94 and 152-154 to the same VBA code? Yes they all work from the same opt button
I am just going to manually delete the extra line that appears on my quote

 

[AlphaFrog]

That is correct - basically there are several different lines that have quote info and option button 2 does need to allow only line 33 to show, but I am going to stick with the original formula you provided for now. I just am not sure on how to add rows 92-94 and 152-154 to the same VBA code? Yes they all work from the same opt button
I am just going to manually delete the extra line that appears on my quote

Okay, I'll ask this one again

What's the difference between Option Buttons 1 and 2 (linked cells aside)? I do not get it.

The logic is confusing...
option button 1 which hide row 34 and 35 only
option button 2 which show row 33 only

If we're only talking about rows 33, 34,and 35, then hiding 34 and 35 is the same as showing 33 only.

 

[mustang70]

Update I got this to work good enough for now with all of the lines - I can manually delete the one line if needed
Here what I did
Thanks for your help today - this is a great forum

Private Sub OptionButton1_Change()
Sheets("quote").Rows("34:35").Hidden = Not Range("U9").Value
Sheets("quote").Rows("93:94").Hidden = Not Range("U9").Value
Sheets("quote").Rows("153:154").Hidden = Not Range("U9").Value
End Sub

 

[mustang70]

I have two more questions on hiding rows

I would now like to do the same thing based on an output from combo box 3 - when combobox H11= "Motorized" I need to un-hide row 17 and 27/28 on a different sheet named "quote" - all rows can be hidden when "motorized" is not shown.
Here is what I have but it's working backwards - I have tried removing the "Not" and it doesn't do anything. This will also need to hide rows 76, 87/88 and 136, 147/148 on my sheet "quote" - all hiden rows are on the sheet labeled "quote"

Private Sub combobox3_Change()
Sheets("quote").Rows("17").Hidden = Not Range("H11") = "motorized"
Sheets("quote").Rows("27:28").Hidden = Not Range("H11") = "motorized"

My second question on hiding rows is somewhat easier and I just need to hide rows 31, 90 and 150 from checkbox2 - which when false needs to hide these 3 rows. T1 is the output from checkbox2

Again all work is done on "sheet1" and the row hiding is done on sheet4 which is named "quote"

Thanks

 

[mustang70]

I did get this to work by adding a true false output from an "If" formula but the probem is my reset button no longer works correctly - the reset button was clearing combo box 3's output
I get 1004 unable to set hidden property of the name class

This is my new code - R6 is bascially true when "motorized" is selected

Private Sub combobox3_Change()
Sheets("quote").Rows("17").Hidden = Not Range("r6").Value
Sheets("quote").Rows("27:28").Hidden = Not Range("r6").Value

Ideas?

 

[AlphaFrog]

Look at you. Had to twist your arm to use VBA at first, and now asking macro code questions. Well done.

The Not command will flip a boolean value; True to False or a False to True

In this line, the Range.Value is a boolean True-False value, so it works
Sheets("quote").Rows("34:35").Hidden = Not Range("U9").Value

In this line, the Not will act on the Range.Value (the text "Motorized" which doesn't work) and not the boolean result of Range = Motorized.
Sheets("quote").Rows("17").Hidden = Not Range("H11") = "motorized"
This is what you want.
Sheets("quote").Rows("17").Hidden = Not (Range("H11") = "motorized")
So Range=Motorized will evaluate to a boolean before the Not command.

Also, you do not have to use range references. You could directly evaluate the value of the control object directly by referencing the control.
Sheets("quote").Rows("17").Hidden = Not (Me.Combobox3.Value = "motorized")
Or this...
Sheets("quote").Rows("17").Hidden = Me.Combobox3.Value <> "motorized"

 

[mustang70]

I tried the code you have above and I can't seem to get it to work - the other problem I have is the reset Macro which wipes the combo box 3 clear causing the error "I get 1004 unable to set hidden property of the name class"

Any ideas on how to do this with out having this tied back to the combo box?

Thanks

 

[AlphaFrog]

I tried the code you have above and I can't seem to get it to work - the other problem I have is the reset Macro which wipes the combo box 3 clear causing the error "I get 1004 unable to set hidden property of the name class"

Any ideas on how to do this with out having this tied back to the combo box?

Thanks

Show your code and what you tried even if it doesn't work. Don't make me guess. It could be a typo or anything. I'm not going to list every possibility.

Getting specific details from you is like pulling teeth. Help me help you.

 

[mustang70]

What I am using now works fine except for the error code that comes up when I do a reset function with a Macro- Basically when combobox3 shows motorized I have an IF statement in R6 that shows true or False =IF(Q15=40,TRUE,FALSE) - the "40" is the output from this formula =IF(H11="gear",20,IF(H11="push",30,IF(H11="motorized",40,"-"))). H11 is the linked cell from combobox3, combo box 3 has 3 selections- motorized, push and geared.

Private Sub combobox3_Change()
Sheets("quote").Rows("17").Hidden = Not Range("r6").Value
Sheets("quote").Rows("27:28").Hidden = Not Range("r6").Value

I tried the other formulas you suggested and the cells go hidden but do not "un-hide" when the selection is changed -My real problem is is that the output from H11 gets deleted during a reset from a macro and this is why I wasn't sure if there was a different way to hide these rows. I read somewhere that the autofill can hide cells but I haven't had any luck with this either.
Just let me know if you need any other info - Thanks

 

[AlphaFrog]

1.) Why delete H11? Change your Reset macro if you need to keep H11.

2.) Why not use this? It should work. I don't get why you want to tie the hidden rows trough formulas when you can easily check the actual combobox value in the code. This is much easier than the overly complex formula method you've configured.

Private Sub combobox3_Change()
Sheets("quote").Rows("17").Hidden = combobox3.Value = "motorized" ' or <> "motorized"
Sheets("quote").Rows("27:28").Hidden = combobox3.Value = "motorized" ' or <> "motorized"