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" wrote in message
...
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" wrote in message
...
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