Clearing, or "unclicking", user created buttons.

[Ancient Wolf]

Can you help me on this one?

I created an Excel sheet that has several macro controlled buttons that were created using the insert shapes tool. Each button will hide/unhide specific rows when clicked. I have another macro controlled button that, in short, resets the page and re-hides any rows that were unhidden. I was given the coding below as a way to identify which buttons the user clicked on. The problem is that if you use the "Reset" button to re-hide rows, then the clicked buttons do not revert. Is there a way to make my "Reset" button undo the color changes? There are 11 buttons that would need to be "unclicked", and are all the same square shape if that helps.

Dim oShape As Shape
Set oShape = ActiveSheet.Shapes(Application.Caller)
If oShape.Fill.ForeColor.RGB = RGB(255, 0, 0) Then
oShape.Fill.ForeColor.RGB = RGB(0, 0, 255)
oShape.TextFrame.Characters.Text = "Unclicked"
With Rows("84:95")
.Hidden = Not .Hidden
End With
Else
oShape.Fill.ForeColor.RGB = RGB(255, 0, 0)
oShape.TextFrame.Characters.Text = "Clicked"
With Rows("84:95")
.Hidden = Not .Hidden
End With
End If

 

[doofusboy]

Do you mean something like this?

Dim oShape As Shape
    For Each oShape In Shapes
        If oShape.TextFrame.Characters.Text = "Clicked" Then
            oShape.Fill.ForeColor.RGB = RGB(0, 0, 255)
            oShape.TextFrame.Characters.Text = "Unclicked"
        End If
    Next oShape

 

[Ancient Wolf]

Thank you for the quick response, Doofusboy. I think that will work just fine.

Now, since each button I created is labeled differently, like TV, Mattress, Audio, would it be possible to cut out the section that changes the text and go strictly by the color changes?

Dim oShape As Shape
For Each oShape In Shapes
If oShape.Fill.ForeColor.RGB = RGB(0, 0, 255)
oShape.Fill.ForeColor.RGB = RGB(255, 0, 0)

End If
Next oShape

Like this?

 

[doofusboy]

You're welcome. And yes, that should work, but you'd need to add the word "Then" where indicated.

Dim oShape As Shape
 
For Each oShape In Shapes
     If oShape.Fill.ForeColor.RGB = RGB(0, 0, 255) [B][COLOR=red]Then[/COLOR][/B]
          oShape.Fill.ForeColor.RGB = RGB(255, 0, 0) 
     End If
Next oShape

 

[Ancient Wolf]

Right. I saw that a little bit later that I missed the "then"

However, I seem to be having a run error. It stops working at this line:

For Each oShape In Shapes

Do you know what I may be doing wrong?

 

[Ancient Wolf]

Thank you again for the help. I was able to fix the error I was running into. I don't know if this was the exact cause, but it seems that the first code I posted used this line:

Set oShape = ActiveSheet.Shapes(Application.Caller)

I had to adjust the line that was giving me the problem to this:

For Each oShape In ActiveSheet.Shapes

So now I have working codes and everyone will be happy. I really appreciate the help you gave me Doofusboy, and all of you at MrExcel.