Hide & Add Rows

[timesareGMT]

HI all,

I'm new in VBA, and I’m trying to make a report layout with the conditions below:

1. If Cell D30 on Sheet 2 = FALSE, automatically hide Row 30 on Sheet 1.
2. If Cell D30 on Sheet 2 = FALSE, automatically add a new row after row 47.

How should I set the code?

Thanks a lot.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
<o></o>
<o></o>
<o></o>

 

[timesareGMT]

What type of checkbox controls did you use on page 2? ActiveX controls from The Controls Toolbox toolbar or Forms controls from the Forms toolbar?

From the Forms toolbar.

Thanks.

 

[AlphaFrog]

Delete the the previous code. I forgot that linked cells don't trigger the Worksheet Change event.

Put this in a standard module and assign this Checker macro to the 5 checkboxes. Right-click on each checkbox and select assign macro.

Sub Checker()
    
    Dim r As Long, cb As CheckBox

    Set cb = ActiveSheet.CheckBoxes(Application.Caller)
    r = Range(cb.LinkedCell).Row + 11
    
    Sheets("page 1").Rows(r).Hidden = cb.Value = xlOff
    If cb.Value = xlOff Then Sheets("page 1").Rows(48).Insert
        
End Sub

 

[timesareGMT]

Delete the the previous code. I forgot that linked cells don't trigger the Worksheet Change event.

Put this in a standard module and assign this Checker macro to the 5 checkboxes. Right-click on each checkbox and select assign macro.

Sub Checker()
 
    Dim r As Long, cb As CheckBox
 
    Set cb = ActiveSheet.CheckBoxes(Application.Caller)
    r = Range(cb.LinkedCell).Row + 11
 
    Sheets("page 1").Rows(r).Hidden = cb.Value = xlOff
    If cb.Value = xlOff Then Sheets("page 1").Rows(48).Insert
 
End Sub

YES!!! It works.

Thank you so much.

 

[timesareGMT]

Delete the the previous code. I forgot that linked cells don't trigger the Worksheet Change event.

Put this in a standard module and assign this Checker macro to the 5 checkboxes. Right-click on each checkbox and select assign macro.

Sub Checker()
 
    Dim r As Long, cb As CheckBox
 
    Set cb = ActiveSheet.CheckBoxes(Application.Caller)
    r = Range(cb.LinkedCell).Row + 11
 
    Sheets("page 1").Rows(r).Hidden = cb.Value = xlOff
    If cb.Value = xlOff Then Sheets("page 1").Rows(48).Insert
 
End Sub

I got some other questions.

I add a new spreadsheet (saying page 3) in the current file, in which there's a combo box offering 2 values (saying GEO and BS).

I try to make the layout:
1. If I select "GEO" from the combo box, row 36 to 48 will hide.
2. If I select "BS", row 19 to 35 will hide.

How should I set the combo box code for this purpose?

Thanks.

 

[AlphaFrog]

Assign this macro to your Forms-type combobox.

Sub Geo_BS()
    Dim dd As DropDown
    Set dd = ActiveSheet.DropDowns(Application.Caller)
    With Sheets("Page 1")
        Select Case UCase(Range(dd.ListFillRange)(dd.Value, 1))
            Case "GEO"
                .Rows("19:35").Hidden = False
                .Rows("36:48").Hidden = True
            Case "BS"
                .Rows("19:35").Hidden = True
                .Rows("36:48").Hidden = False
            Case Else
                .Rows("19:48").Hidden = False
        End Select
    End With
End Sub

 

[timesareGMT]

Assign this macro to your Forms-type combobox.

Sub Geo_BS()
    Dim dd As DropDown
    Set dd = ActiveSheet.DropDowns(Application.Caller)
    With Sheets("Page 1")
        Select Case UCase(Range(dd.ListFillRange)(dd.Value, 1))
            Case "GEO"
                .Rows("19:35").Hidden = False
                .Rows("36:48").Hidden = True
            Case "BS"
                .Rows("19:35").Hidden = True
                .Rows("36:48").Hidden = False
            Case Else
                .Rows("19:48").Hidden = False
        End Select
    End With
End Sub

Thanks for the info.

But sorry, I forgot to tell you that the hiding effect is within the same page (page 3).

I replaced the "Page 1" above with "Page 3", but nothing can be seen.

Have I missed something?

 

[AlphaFrog]

What's the name of the combobox?

What is the Input range used to populate the combobox?

Are the two items in the Input range exactly GEO and BS? No leading or trailing spaces.

 

[timesareGMT]

What's the name of the combobox?

What is the Input range used to populate the combobox?

Are the two items in the Input range exactly GEO and BS? No leading or trailing spaces.

...I don't know there's a name for the combobox... I used the Form toolbar to insert the combobox on the spreadsheet.

Input range = $B$55:$B$56
Cell link = $B$57

The words "GEO" & "BS" were input in the cell B55 & B56 respectively.

The result of the combo box selection was refected in the cell C2 with the formula "=INDEX(B55:B56,B57)"

So what I'm trying to do is that:
1. If "GEO" was selected, C2 will show "GEO" and row 35 to 47 on the same page will hide automatically.
2. If "BS" was selected, C2 will show "BS" and row 18 to 34 on the same page will hide automatically.

Thanks for your help.

 

[AlphaFrog]

It should work. Double check that you assigned the correct maco to the combobox. The macro should be located in a standard module.

Here is a more simplified macro.

Sub Geo_BS()
    Select Case UCase(Trim(Range("C2"))).Value
        Case "GEO"
            Rows("19:35").Hidden = False
            Rows("36:48").Hidden = True
        Case "BS"
            Rows("19:35").Hidden = True
            Rows("36:48").Hidden = False
        Case Else
            Rows("19:48").Hidden = False
    End Select
End Sub

 

[timesareGMT]

It should work. Double check that you assigned the correct maco to the combobox. The macro should be located in a standard module.

Here is a more simplified macro.

Sub Geo_BS()
    Select Case UCase(Trim(Range("C2"))).Value
        Case "GEO"
            Rows("19:35").Hidden = False
            Rows("36:48").Hidden = True
        Case "BS"
            Rows("19:35").Hidden = True
            Rows("36:48").Hidden = False
        Case Else
            Rows("19:48").Hidden = False
    End Select
End Sub

After making selection from the combobox, the screen shows:
"Run-time error '424': Object required"

Some info missed?

Thanks.