Dependent Macro/Toggle Buttons

[Guest]

I have a table with about 20 rows. Rows 1 to 5 are always visible. I have
added 2 toggle buttons, one to expand and collapse rows 6-12 and the second
one to expand rows 13-20. Is there any way I can program toggle button 2 to
become available only after the user has clicked on toggle button 1?

Also, if the user does not enter data in any of rows 6-20, upon closing the
workbook or before printing the worksheet, I want rows 6-20 to be hidden.
(Hope that is not too complex, I barely have programming skills but can
understand if it is explained in simple terms) Much appreciation for any help

 

[Tom Ogilvy]

right click on the sheet tab an put in code like this


----- In the sheet module (right click on the sheet tab and select view
code)
Private Sub ToggleButton1_Click()
If Me.ToggleButton1.Value = True Then
Range("6:12").Entirerow.Hidden = True
Me.ToggleButton2.Enabled = True
Else
Range("6:12").EntireRow.Hidden = False
Me.ToggleButton2.Value = False
Me.ToggleButton2.Enabled = False
End If
End Sub

Private Sub ToggleButton2_Click()
if me.ToggleButton2.Value = True
Range("13:20").EntireRow.Hidden = True
else
Range("13:20").EntireRow.Hidden = False
end if
end Sub

Private Sub Worksheet_Activate()
Me.ToggleButton2.Enabled = False
End Sub


---------- In the ThisWorkbook Module

Private Sub Workbook_Open
Worksheets("Sheet2").Activate
Worksheets("Sheet1").Activate
end Sub


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
ProcSheet1
End Sub

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If Activesheet.Name = "Sheet1" then
ProcSheet1
End if
End Sub
---------
In a general Module

Sub ProcSheet1()
With Worksheets("Sheet1")
Set rng1 = Range("6:12").EntireRow
Set rng2 = Range("13:20").EntireRow
set sh = Worksheets("Sheet1")
If Application.CountA(rng1) = 0 Then
sh1.ToggleButton1.Value = True
rng1.Hidden = True
Else
sh1.toggleButton1.Value = False
rng1.Hidden = False
End If
If Application.CountA(rng2) = 0 Then
sh1.ToggleButton2.Value = True
rng2.Hidden = True
Else
sh1.ToggleButton2.Value = False
rng2.Hidden = False
End If
End Sub

--
Regards,
Tom Ogilvy

I have a table with about 20 rows. Rows 1 to 5 are always visible. I have
added 2 toggle buttons, one to expand and collapse rows 6-12 and the second
one to expand rows 13-20. Is there any way I can program toggle button 2 to
become available only after the user has clicked on toggle button 1?

Also, if the user does not enter data in any of rows 6-20, upon closing the
workbook or before printing the worksheet, I want rows 6-20 to be hidden.
(Hope that is not too complex, I barely have programming skills but can
understand if it is explained in simple terms) Much appreciation for any

help

 

[Guest]

Tom, thank you very much for the code. It worked perfectly!!!!!! I have
very little programming knowledge but it is so awesome when I get answers for
what I need. If I had known I would enjoy playing with programming this much,
I would have done programming in college. Thank you again.