JS5
12-21-2010, 11:02 AM
Greetings everybody,
I want to do a list of items with subitems. For that I created a button that can show/hide lines under it by clicking. I want to do something like this in excel:
Item 1
+ Item 2
- Item 3
Item 3.1
-Item 3.2
Item 3.2.1
+Item 3.2.2
Item 4
It already works with the macro exposed in the end, and it reads the value of the right side neighbor cell for vba knows how many lines its going to be hidden.
My problem is that when I have a button in line 10, for example, and I want to hide lines from 11 to 20, i can not hide any buttons that might be on those lines. When i click the button at line 10, any button on line 11 to 20 goes to line 21 right under the line 10.
Does anybody know a way of fixing it by hiding the button(s) together with the hidden lines OR another way of doing this list???
Remember that my button can be copy/paste to any cell and still hide any number of lines under it by reading the righside cell.
Well...thanks in advance for any help!
JS5
Sub Botão1_Clique()
Dim BTN As Button
Dim i As Integer
Set BTN = ActiveSheet.Buttons(Application.Caller)
i = BTN.TopLeftCell.Offset(0, 1).Value
If LCase(BTN.Caption) = LCase("Hide Rows") Then
BTN.TopLeftCell.Offset(1, 0).Resize(i, 1).EntireRow.Hidden = True
BTN.Caption = "Show Rows"
Else
BTN.TopLeftCell.Offset(1, 0).Resize(i, 1).EntireRow.Hidden = False
BTN.Caption = "Hide Rows"
End If
End Sub
I want to do a list of items with subitems. For that I created a button that can show/hide lines under it by clicking. I want to do something like this in excel:
Item 1
+ Item 2
- Item 3
Item 3.1
-Item 3.2
Item 3.2.1
+Item 3.2.2
Item 4
It already works with the macro exposed in the end, and it reads the value of the right side neighbor cell for vba knows how many lines its going to be hidden.
My problem is that when I have a button in line 10, for example, and I want to hide lines from 11 to 20, i can not hide any buttons that might be on those lines. When i click the button at line 10, any button on line 11 to 20 goes to line 21 right under the line 10.
Does anybody know a way of fixing it by hiding the button(s) together with the hidden lines OR another way of doing this list???
Remember that my button can be copy/paste to any cell and still hide any number of lines under it by reading the righside cell.
Well...thanks in advance for any help!
JS5
Sub Botão1_Clique()
Dim BTN As Button
Dim i As Integer
Set BTN = ActiveSheet.Buttons(Application.Caller)
i = BTN.TopLeftCell.Offset(0, 1).Value
If LCase(BTN.Caption) = LCase("Hide Rows") Then
BTN.TopLeftCell.Offset(1, 0).Resize(i, 1).EntireRow.Hidden = True
BTN.Caption = "Show Rows"
Else
BTN.TopLeftCell.Offset(1, 0).Resize(i, 1).EntireRow.Hidden = False
BTN.Caption = "Hide Rows"
End If
End Sub