Dependent Macro/Toggle Buttons

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

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


"Chiku" <[email protected]> wrote in message
news:[email protected]...
> 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, 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.

"Tom Ogilvy" wrote:

> 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
>
>
> "Chiku" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>