Bulk Grouped Shape Property Changes

[Ark68]

I am pursuing adding "buttons" to my worksheet with macros assigned to initiate certain tasks. I have a series of 8 buttons, each one a grouped rounded rectangle box and text box. I am new to working with shapes.

I am looking for a VBA solution to :

Set my 8 buttons, without doing one by one, to their default (unselected) state of no fill, red text, red .25 border. The macros have been assigned to each button already. This code will be executed initially when the worksheet is activated to set the buttons to their default state. I'm thinking a loop of sorts for each group shape? I'm unsure how to use the loop to target each individual group though.


When the user clicks on the group, the button changes to its selected state of blue fill, white text and green .75 border. Of course the macro will trigger.

In addition, there may be a time when I want to disable a particular button. How would I accomplish that?

 

[Dan_W]

I would suggest using the Macro Recorder. From the moment you turn it on, it will monitor your actions, and then generate the VBA code to recreate those actions/reproduce those results for later use. If you can make the necessary changes to one of the shapes, as you set out above, the macro recorder will produce a subroutine for you with all the relevant property adjustments. If it is something that is to apply to all the shapes, then select them all and make the changes. From that point, it is an easier to task to tidy up the code and make it reliably reusable going forward. There are a number of guides / explanations written by others on this forum about how to make the best use of the Macro Recorder, but do let me know if you have difficulty finding one.

 

[Ark68]

Thank you Dan. I gave the macro recorder a shot. But it left me with mixed results.

1. My button consists of a a rounted rectangle shape (no background color and a formatted border line), and a textbox superimposed over it (no fill, no line) and then grouped (group27). When I changed the fill of the shape, both the shape and textbox filled with the color. Both the rectangle and textbox also took the line formatting. I don't want the textbox to have a line. For me to centre the text in the shape, the textbox is slighly askew so the result looks odd as it's filled also, and no longer is within the shape.

The image above shows the formatting after going through the steps, while the lower "OVC" shows what the buttoins look before being formatted.

Here's the code that the macro recorder created. Not quite sure how to deciphre to adapt and reduce.

ActiveSheet.Shapes.Range(Array("Group 27")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorAccent1
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0
        .Solid
    End With
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorAccent4
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0.400000006
        .Transparency = 0
        .Solid
    End With
    With Selection.ShapeRange.Line
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorAccent1
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0
    End With
    With Selection.ShapeRange.Line
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorAccent1
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = -0.25
        .Transparency = 0
    End With
End Sub

I assume this code is changing both the shape and the textbox of the grouped object. Is this how it has to be done? To format both components of the group individually? And if so, how do I know which is the rectangle and which is the textbox so that I can set the setting accordingly?

And second issue, I have 8 grouped buttons that I have to format. DO I have to do each one individually, or is there a way I can loop through all 8 and share the code. I'm thinking a loop, but I'm not sure how to sequentially loop through all 8.

Sorry for the questions? This is a new concept I'm trying to grasp.

 

[Dan_W]

Working with the Macro Recorder can be a bit of an artform - it can take some time to understand exactly what it will/will not record, and how it will record things. As a general rule, everything it does is verbose - it will often list record properties of things you do/make that you did not change. Once you get the hang of it, it can be extremely helpful.

In terms of what you're trying to do, it's a bit to hard to help without seeing for myself, and the code above doesn't tell me a whole lot. The first line:

ActiveSheet.Shapes.Range(Array("Group 27")).Select

tells me that had selected a group of shapes called Group 27, and that you then changed certain properties of that group of shapes. Exactly as you have you assumed above. It isn't how this has to be done, though - you simply need to Ungroup the selection (or not group it in the first place, which may have been done by the Macro Recorder - I can't see the code, so I don't know). Once you've ungrouped the Shape Group, the Macro Recorder will monitor the individual shapes.

You'll see this when dealing with code created by Macro Recorder, there is a lot of Select and Selection - this can become annoying and difficult to follow, so it is worth going through and replacing them with the actual objection that is being referred to by the Selection keyword. In your case, in will be the Shape that you're working on.

If you didn't want to ungroup the shape group, you can work on individual shapes by referencing them by name. So, in your example, the Rounded Rectangle might have the name "Rounded Rectangle 1", and so you could assign that shape to a variable to make it easier to use in code:

Dim RoundRect As Shape
Set RoundRect = Activesheet.Shapes("Rounded Rectangle 1")
RoundRect.Fill.Forecolor.RGB = vbWhite

For example, this will take an existing shape called Rounded Rectangle 1, and change it to white. I could then use the variable RoundRect in place of those instances of Select and Selection (that were refering to this shape) to make the code easier to understand.

No problem about the questions, I just didn't have much time today (thus explaining the scatter gun response above). Does the above generally make sense though? If you have some code that manages to get you close enough to a working solution, I can try and help you clean it up and make it easier to use.

 

[Dan_W]

In addition, there may be a time when I want to disable a particular button. How would I accomplish that?

Sorry - I didn't even clock this question - though I appreciate we're not quite at the stage that this needs to be dealt with. There are a number of ways you could do this - but I suppose the best solution depends on what it is that would trigger the disabling of the button. When would the user be able/unable to use it?

 

[Ark68]

Hello Dan, thank you for the explanation. It helped understand a few more concepts I was unfamiliar with.
It turns out I found a solution by just tinkering around. It's not pretty but it works.

I first assigned all the rounded rectangles of the 11 groups a unique and sequential name. wthr_btn_1, wthr_btn_2 and so on to wthr_btn_11.
I created a macro for each button, and assigned it to their respective button. I had to make 11 seperate macros, each assigned to it's individual button. Each macro differed only in a few lines. Here is one of the 11 macros ...

Sub btn_clr()
    Dim wttext As String, wtsel As String, wthr_btn As String, ultrigger As Boolean
    wttext = "Clear"
    wtsel = "CLR "
    wthr_btn = "wthr_btn_11"
    btn_format wttext, wtsel, wthr_btn, ultrigger
End Sub

Each macro calls the routine btn_format which is used to format the button based on the current state. This gives mimics toggling of the button.

ub btn_format(ByRef wttext As String, wtsel As String, wthr_btn As String, ultrigger As Boolean)
    mbevents = False
    If ws_gui.Shapes(wthr_btn).Fill.ForeColor = vbWhite Then
        'MsgBox wttext
        With ws_gui.Shapes(wthr_btn)
            .Fill.ForeColor.RGB = RGB(207, 244, 234)  'grey-blue
            .Line.Weight = 0.75
            .Line.ForeColor.RGB = RGB(112, 163, 192) 'Palette C4R4
        End With
        wthr_str = wthr_str & wtsel
        ultrigger = True
    Else
        MsgBox "Default"
        With ws_gui.Shapes(wthr_btn)
            .Fill.ForeColor.RGB = vbWhite
            .Line.Weight = 0.25
            .Line.ForeColor.RGB = vbBlack
            If Len(wthr_str) > 0 Then
                sp = InStr(wthr_str, wtsel)
                If sp > 0 Then
                    wthr_str = Replace(wthr_str, wtsel, "")
                    MsgBox wthr_str & " (" & Len(wthr_str) & ")"
                End If
            End If
        End With
        ultrigger = False
    End If
    With ws_gui
        .Unprotect
        .Range("R13") = wthr_str
        'MsgBox wthr_str & " (" & Len(wthr_str) & ")"
        .Protect
    End With
    mbevents = True
End Sub

I am faced with a lingering challenge though. When the worksheet activates, I do not want these buttons functional. So I have placed code in my worksheet "initialization" code that resets the buttons to their default format stripped of their macros, thus essentially disabling them. This isn't working. I've posted for assistance with this problem here: Error Trying To Remove Macro Assignment to Shapes