a_madman
05-20-2009, 02:17 PM
I have a spreadsheet that lists options for a specific product. A product is selected from a drop down list and then the following rows are populated with the appropriate option pricing. If an option in the list does not apply to the selected product, then that row is hidden by a macro tied to the drop down list. The result is a price list of all the options for the selected product and a total.
I would like to add the abillity to hide a row at the click of a button that would allow a user to hide options they don't want, resulting in the total price for a product built to their specifications.
I'm currently trying to use a button to hide the individual rows containing options, but I can't get the button to hide with the row when clicked.
Here's the code I'm working with. The first worksheet_change macro is tied to the product drop down list and hides all options not related to the selected product. Button2 is what I want to use to hide an individual row as well as the button itself. Button3 unhides everything in case the user wants to start over. Thanks in advance for any input.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$3" Or Target.Address = "$E$3" Then
ActiveSheet.Cells.EntireRow.Hidden = False
Dim x
For x = 8 To 42
If Cells(x, 3) = "" And Cells(x, 5) = "" Then
Rows(x).EntireRow.Hidden = True
End If
Next
End If
End Sub
Sub Button2_Click()
ActiveSheet.Rows("8:8").Hidden = True
End Sub
Sub Button3_Click()
ActiveSheet.Rows("8:42").Hidden = False
End Sub
I would like to add the abillity to hide a row at the click of a button that would allow a user to hide options they don't want, resulting in the total price for a product built to their specifications.
I'm currently trying to use a button to hide the individual rows containing options, but I can't get the button to hide with the row when clicked.
Here's the code I'm working with. The first worksheet_change macro is tied to the product drop down list and hides all options not related to the selected product. Button2 is what I want to use to hide an individual row as well as the button itself. Button3 unhides everything in case the user wants to start over. Thanks in advance for any input.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$3" Or Target.Address = "$E$3" Then
ActiveSheet.Cells.EntireRow.Hidden = False
Dim x
For x = 8 To 42
If Cells(x, 3) = "" And Cells(x, 5) = "" Then
Rows(x).EntireRow.Hidden = True
End If
Next
End If
End Sub
Sub Button2_Click()
ActiveSheet.Rows("8:8").Hidden = True
End Sub
Sub Button3_Click()
ActiveSheet.Rows("8:42").Hidden = False
End Sub