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>

 

[VoG]

Try

Sub btest()
With Sheets("Sheet1")
    If Sheets("Sheet2").Range("D30").Value = False Then
        .Rows(30).Hidden = True
        .Rows(48).Insert
    End If
End With
End Sub

 

[AlphaFrog]

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

Add a new row on which sheet; Sheet1 or Sheet2?

 

[timesareGMT]

Add a new row on which sheet; Sheet1 or Sheet2?

Sheet 1. Thanks.

 

[timesareGMT]

Try

Sub btest()
With Sheets("Sheet1")
    If Sheets("Sheet2").Range("D30").Value = False Then
        .Rows(30).Hidden = True
        .Rows(48).Insert
    End If
End With
End Sub

Thanks for your advice.

But have I made a mistake? Or I've missed some steps? .... the code doesn't seem working...

I right click the sheet 1 tab, entering [view code], copy & paste your code and press [ALT+Q]

But nothing's happening, no indication of change on the layout or even bugs or others.

 

[AlphaFrog]

I right click the sheet 1 tab, entering [view code], copy & paste your code and press [ALT+Q]

But nothing's happening, no indication of change on the layout or even bugs or others.

It sounds like you want an Event macro to automatically run when you make a change to the sheet. Is that correct? You didn't indicate that in your original post.

VoG's macro is a standard macro that you have to run manually. It would be located in a standard VBA module and not in the worksheet module.

If you want an auto-executing event macro, you have to define what event(s) will trigger it.

You have a formula in Sheet2 D30 that returns TRUE or FALSE. What is that formula? An event macro could be made to execute when you make a change to one of the precedent cells in the formula e.g if the formula was say =A1=B1, both A1 and B1 are precedents. So in this example, an event macro could be made to run when you make a change to A1 or B1 and then does something based on the formula result.

Also, do you need this to work for other cells (not just D30) as well? Please provide details.

 

[timesareGMT]

It sounds like you want an Event macro to automatically run when you make a change to the sheet. Is that correct? You didn't indicate that in your original post.

VoG's macro is a standard macro that you have to run manually. It would be located in a standard VBA module and not in the worksheet module.

If you want an auto-executing event macro, you have to define what event(s) will trigger it.

You have a formula in Sheet2 D30 that returns TRUE or FALSE. What is that formula? An event macro could be made to execute when you make a change to one of the precedent cells in the formula e.g if the formula was say =A1=B1, both A1 and B1 are precedents. So in this example, an event macro could be made to run when you make a change to A1 or B1 and then does something based on the formula result.

Also, do you need this to work for other cells (not just D30) as well? Please provide details.

Yes, you've got to my point. Thanks.

Actually, it's report format consisting of 2 pages, one being the data input page and another the report layout.
The report layout is linked to the data input page and is for printout only, all the data entry plus the related calculations are done within the data input page.

I set up 5 check boxes on page 2, linking to 5 single cells ranging D30 to D34 on the same page. They will show "TRUE" when checked and "FALSE" when unchecked.
These 5 cells are linked to another 5 cells ranging D41 to D45 on page 1 respectively.

What I am trying to do is that:
1. When anyone or all of the 5 check boxes on page 2 are being unchecked, the rows 41 to 45 on page 1 will automatically hide.
2. Once a checkbox on page 2 is being unchecked, a new row will be inserted accordingly after the row 47 on page 1.

Will there be a solution?

Thanks again.

 

[AlphaFrog]

Right-click on the Sheet1 tab and select View Code . Paste this code in the Sheet1 VBA module. It uses the linked cells on Sheet1 to trigger the macro.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("D41:D45"), Target) Is Nothing Then
        Target.EntireRow.Hidden = Target.Value = False
        If Target.Value = False Then Rows(48).Insert
    End If
End Sub

 

[timesareGMT]

Right-click on the Sheet1 tab and select View Code . Paste this code in the Sheet1 VBA module. It uses the linked cells on Sheet1 to trigger the macro.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("D41:D45"), Target) Is Nothing Then
        Target.EntireRow.Hidden = Target.Value = False
        If Target.Value = False Then Rows(48).Insert
    End If
End Sub

Thanks.

But I have tried, it's not working. The hiding & inserting function are not working.

When the 5 checkboxes are unchecked, D30 to D34 on page 2 will show "FALSE", and it will be refected in the cells D41 to D45 directly on page 1. Since I just used the formulae "='page 2'!D30:D34".

But after applying the macro, nothing's happening.

Your further guidance is sought.

Thanks.

 

[AlphaFrog]

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?