PDA

View Full Version : Solved: Hide button with associated row



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

MaximS
05-21-2009, 04:34 AM
Can you please post sample workbook and explain what is not working in your code?

a_madman
05-21-2009, 05:41 AM
I've attached an example file. Cells C3 and C5 are drop down lists to select the product. I want the hide button, as shown in cell H8, to hide row 8 AS WELL AS the button itself when clicked. That's what I can't get to work. Right now if you click the "Hide Row" button it will hide the row, but the button will still be there.

MaximS
05-21-2009, 06:48 AM
i've changed your conrols and should work now. see attachment for details.

a_madman
05-21-2009, 07:16 AM
Thanks Maxim, you rock. Can you help me understand what I was doing wrong? I actually tried the .visible control and was getting errors. Was it the object name? Or the order of the vba code? Or the lack of "Private" before the code?

MaximS
05-21-2009, 07:48 AM
nothing like that i've just used different types of controls - available from toolbox (yours were form controls - they don't have properties to set)

a_madman
05-26-2009, 07:05 AM
Here's the final version of the example file, in case someone else is looking for something similar in the future. It will automatically hide options that do not apply to the selected products and then it also presents a "hide" button next to each of the remaining options so they can be removed as desired. A total price is then displayed for the remaining options.