VBA Shape Color Fill

[3ddesignbros]

So I'm writing some VBA code so that when certain things happen certain shapes change to different colors.

Example 1 is a shape-changing color based on if a sheet is visable or not:

If ThisWorkbook.Sheets("FlashForge").Visible = True Then
        ThisWorkbook.Sheets("Farm Data").Shapes("FlashforgeButton").Fill.ForeColor.RGB = RGB(139, 217, 139)
    Else
        ThisWorkbook.Sheets("Farm Data").Shapes("FlashforgeButton").Fill.ForeColor.RGB = RGB(255, 255, 255)
    End If

This code is working just how it should:

Example 2 is right under example 1 but is a shape-changing color based on if certain rows in a sheet are hidden or not:

If ThisWorkbook.Sheets("Filament Data Sheet").Rows("183:194").EntireRow.Hidden = False Then
        ThisWorkbook.Sheets("Farm Data").Shapes("Fila16button").Fill.ForeColor.RGB = RGB(139, 217, 139)
    Else
        ThisWorkbook.Sheets("Farm Data").Shapes("Fila16button").Fill.ForeColor.RGB = RGB(255, 255, 255)
    End If

This one is not working.

So I know it must be something to do with how I'm hidden the rows but I can't figure it out. Any help would be appreciated!

 

[Jon Peltier]

You say it's "not working" without describing what it does.

I just did a little test, like this in the Immediate Window:

? activesheet.rows("5:10").entirerow.hidden

It seems that if the first row in activesheet.rows("5:10") is hidden, then the expression is True, even if most of the rows are not hidden. If you need all of the rows to be hidden for a certain color to be used, you'll have to loop through all and test each row to see if it's hidden.

 

[3ddesignbros]

I'll try to explain further. Below is the code in question.

If ThisWorkbook.Sheets("Filament Data Sheet").Rows("183:194").EntireRow.Hidden = False Then
        ThisWorkbook.Sheets("Farm Data").Shapes("Fila16button").Fill.ForeColor.RGB = RGB(139, 217, 139)
    Else
        ThisWorkbook.Sheets("Farm Data").Shapes("Fila16button").Fill.ForeColor.RGB = RGB(255, 255, 255)
    End If

My goal is if rows 183:194 are visible it will change the shape box to RGB(139,217,139) which is a light great. If the rows are hidden it will change the box to RGB (255,255,255) which is white.

If I run the current code above it changes the box to the light green whether the rows are visible or hidden.

If I change the code to say "True" rather than "False" then it does the opposite and changes the box to be white regardless of the visibility of the rows. However, it never alternates between Green and White based on the visibility of the rows which is my goal.

One side note: the rows in question are hidden or unhidden by the use of a separate VBA. Not sure if that would matter or not but trying to give as much info as I can.

 

[Jon Peltier]

Like I said, using SomeMultipleRowRange.EntireRow.Hidden is not valid, since it will return True if just the first row is hidden, even if subsequent rows in the range are not hidden.

You could test whether SomeMultipleRowRange.Height is zero:

    If ThisWorkbook.Sheets("Filament Data Sheet").Rows("183:194").Height > 0 Then
        ThisWorkbook.Sheets("Farm Data").Shapes("Fila16button").Fill.ForeColor.RGB = RGB(139, 217, 139)
    Else
        ThisWorkbook.Sheets("Farm Data").Shapes("Fila16button").Fill.ForeColor.RGB = RGB(255, 255, 255)
    End If

 

[3ddesignbros]

Thanks for the reply @Jon Peltier the whole multiple range not being valid issue you bring up shouldn't be a problem. The way the spreadsheet is set up it's not possible for row 183 to be hidden without the remaining rows being hidden as well. So even if the code is only looking at the first row it would still communicate what I need.

I've even gone and changed the command to just look at one row and I get the same results as I stated above where it will change the color once but will not flip back and forth based on if the row or rows are hidden.

I tried your code above about looking at the height. I get the same results it initially changes the shape color to the light green when the rows are unhidden. When they get hidden again though the shape doesn't get changed back to white rather it stays the light green which should not be the case.

 

[Jon Peltier]

Do you run the code again after hiding the rows?

 

[3ddesignbros]

Yes, the code is triggered when the sheet is selected. I've also run it manually just to make sure and the results don't change from what I've stated above.

 

[Jon Peltier]

Okay, I have replicated your workbook, with sheets 'Filament Data Sheet' and 'Farm Data', with a rectangle named "Fila16button" on 'Farm Data'. I hide and unhide rows 183:195 on 'Filament Data Sheet' and I have the following code in the 'Farm Data' code module:

Private Sub Worksheet_Activate()
    If ThisWorkbook.Sheets("Filament Data Sheet").Rows("183:194").Height > 0 Then
        ThisWorkbook.Sheets("Farm Data").Shapes("Fila16button").Fill.ForeColor.RGB = RGB(139, 217, 139)
    Else
        ThisWorkbook.Sheets("Farm Data").Shapes("Fila16button").Fill.ForeColor.RGB = RGB(255, 255, 255)
    End If
End Sub

The rectangle changes color every time I activate 'Farm Data', depending on the status of rows 183:195 in 'Filament Data Sheet'.

I don't know what else to suggest.

 

[3ddesignbros]

Thanks, @Jon Peltier I'm pretty stumped as well.

Is there a way to have multiple "Worksheet_Activate" subs on one page?

I ask because I have a lot of shapes on this page that all change color depending on what happens and I currently have them all nested under one "Worksheet_Activate" and I'm wondering if that's the issue.

Private Sub Worksheet_Activate()

    If ThisWorkbook.Sheets("Creality3D").Visible = True Then
        ThisWorkbook.Sheets("Farm Data").Shapes("CrealityButton").Fill.ForeColor.RGB = RGB(139, 217, 139)
    Else
        ThisWorkbook.Sheets("Farm Data").Shapes("CrealityButton").Fill.ForeColor.RGB = RGB(255, 255, 255)
    End If
     If ThisWorkbook.Sheets("Prusa").Visible = True Then
        ThisWorkbook.Sheets("Farm Data").Shapes("PrusaButton").Fill.ForeColor.RGB = RGB(139, 217, 139)
    Else
        ThisWorkbook.Sheets("Farm Data").Shapes("PrusaButton").Fill.ForeColor.RGB = RGB(255, 255, 255)
    End If
    If ThisWorkbook.Sheets("Prusa").Visible = True Then
        ThisWorkbook.Sheets("Farm Data").Shapes("PrusaButton").Fill.ForeColor.RGB = RGB(139, 217, 139)
    Else
        ThisWorkbook.Sheets("Farm Data").Shapes("PrusaButton").Fill.ForeColor.RGB = RGB(255, 255, 255)
    End If
    If ThisWorkbook.Sheets("Elegoo").Visible = True Then
        ThisWorkbook.Sheets("Farm Data").Shapes("ElegooButton").Fill.ForeColor.RGB = RGB(139, 217, 139)
    Else
        ThisWorkbook.Sheets("Farm Data").Shapes("ElegooButton").Fill.ForeColor.RGB = RGB(255, 255, 255)
    End If
    If ThisWorkbook.Sheets("AnyCubic").Visible = True Then
        ThisWorkbook.Sheets("Farm Data").Shapes("AnyCubicButton").Fill.ForeColor.RGB = RGB(139, 217, 139)
    Else
        ThisWorkbook.Sheets("Farm Data").Shapes("AnyCubicButton").Fill.ForeColor.RGB = RGB(255, 255, 255)
    End If
    
    If ThisWorkbook.Sheets("FlashForge").Visible = True Then
        ThisWorkbook.Sheets("Farm Data").Shapes("FlashforgeButton").Fill.ForeColor.RGB = RGB(139, 217, 139)
    Else
        ThisWorkbook.Sheets("Farm Data").Shapes("FlashforgeButton").Fill.ForeColor.RGB = RGB(255, 255, 255)
    End If
    
    If ThisWorkbook.Sheets("Filament Data Sheet").Rows("183:194").Height > 0 Then
        ThisWorkbook.Sheets("Farm Data").Shapes("Fila16button").Fill.ForeColor.RGB = RGB(139, 217, 139)
    Else
        ThisWorkbook.Sheets("Farm Data").Shapes("Fila16button").Fill.ForeColor.RGB = RGB(255, 255, 255)
    End If
    
End Sub

So all the commands that are based on a sheet being visible or not work as intended but then when I get down to the row one I get the issue we've been discussing.
Do I have too many under the one Sub? Can I make a second sub that is a worksheet_Activate in the same page module?

 

[Jon Peltier]

No, there can be just one Worksheet_Activate sub per worksheet. But I don't think that's the problem.

What if you comment out all but our particular If clause? Does it still not work?