Error trying to change font colour of textbox part of grouped object

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
4,616
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I have a series of 7 grouped shapes (named: btn_svc_# respectively) object on my worksheet (using as a button) that comprises of a rounded rectangles (named: srv_btn_# respectively) and a superimposed textbox (named: srv_tb_#) where # isolates the unique "button".

I am using this code to set the default format properties of each of the seven buttons:

Rich (BB code):
        For sbtn = 1 To 7
            ssbtn = "srv_btn_" & sbtn
            Set shp = .Shapes(ssbtn)
            With shp
                .Fill.ForeColor.RGB = vbWhite
                .Line.Weight = 0.25
                .Line.ForeColor.RGB = RGB(209, 239, 250)
            End With
            .Shapes("svc_tb_" & sbtn).Font.Color = RGB(209, 239, 250)
        Next sbtn

I am getting a "The item with the specified name wasn't found." error with the line in red.

I'm just guessing, but I might have to ungroup the grouped object to make svc_tb_# accessible? I looked, but I haven't figured out how to do that. Looking for help.
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
look what kind of shape you have, grouped or not and find the wanted shape
This was my trial, so adapt it to your situation.
VBA Code:
Sub Macro1()
     myname = "svc_tb_" & sbtn                                  'your shapename
     myname = "Rectangle 1"                                     'my shapename

     With ActiveSheet                                           'this sheet
          For i1 = 1 To .Shapes.Count                           'loop through all the shapes
               With .Shapes(i1)                                 'a shape
                    b = (InStr(.Name, "Group") <> 0)            'check if the name is "Group ..."
                    If b Then                                   'in case of a group
                         For i2 = 1 To .GroupItems.Count        'loop through the groupitems
                              If StrComp(.GroupItems(i2).Name, myname, vbTextCompare) = 0 Then     'is this the wanted shape
                                   .GroupItems(i2).Fill.ForeColor.RGB = RGB(209, 239, 250)     'change a property
                              End If
                         Next
                    Else                                        'not in a group
                         If StrComp(.Name, myname, vbTextCompare) = 0 Then     'is this the wanted shape
                              .Fill.ForeColor.RGB = RGB(209, 239, 250)     'change a property
                         End If
                    End If
               End With
          Next

     End With
End Sub
 
Upvote 0
It looks like it should be "srv_tb_" & sbtn, not "svc_tb_" & sbtn correct? If so, then try . . .

VBA Code:
.Shapes("srv_tb_" & sbtn).TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(209, 239, 250)

Hope this helps!
 
Upvote 0
Hello folks, thank you both!
Domenic, unfortunately, even with that obvious correction, the result was the same. Error.

Bsalv, I've modified my code based on my interpretation of your suggestion, but it's stopping with an error ("This member can only be accessed for a group") with the line in red.
Rich (BB code):
           ssbtn = "btn_srv_" & sbtn          'btn_srv_1
           With .Shapes(ssbtn)     
                b = (InStr(.Name, "srv_btn") <> 0)   'true: srv_tb_1 exists in group btn_srv_1
                If b Then                                   'in case of a group
                    For i2 = 1 To .GroupItems.Count        'Error:  This member can only be accessed for a group 'loop through the groupitems
                        If StrComp(.GroupItems(i2).Name, ssbtb, vbTextCompare) = 0 Then     'is this the wanted shape
                            .GroupItems(i2).Font.Color = RGB(209, 239, 250)    'change a property
                        End If
                    Next
                Else                                        'not in a group
                    If StrComp(.Name, ssbtb, vbTextCompare) = 0 Then     'is this the wanted shape
                        .Font.Color = RGB(209, 239, 250)     'change a property
                    End If
                End If
            End With
            '.Shapes("srv_tb_" & sbtn).Font.Color = RGB(209, 239, 250) 'Domenic's correction commented out for testing


I've attached a picture of my selection pane so the structure of my objects can be referred to. btn_srv_# is thr grouped item composed of srv_tb_# is the text box for which I am trying to change the font colour of, and srv_btn_# is the rounded rectangle shape (which I have no problem changing it's properties just through btn_srv_#.


Capture2.JPG
 
Upvote 0
i'm not familiar with that screenshot.
Are that all the groups and a level lower, the elements (groupitems) within that group.
So, you renamed your groups ?
 
Upvote 0
i try to do the same thing, make a tree of your shapes starting in cell AA1
So look withing a grouped shape for a groupitem with your name or if it's not grouped, for a shape with your name
VBA Code:
Sub My_Groups()
     myname = "btn_srv_1"                                       'your wanted shapename
     'myname = "heart 6"                                         'my shapename

     Set dict = CreateObject("scripting.dictionary")            'just tempory add names to a dictionary
     dict.Add dict.Count, Array("number", "shape/group name", "groupitem")
     With ActiveSheet                                           'this sheet
          For i1 = 1 To .Shapes.Count                           'loop through all the shapes
               With .Shapes(i1)                                 'a shape
                    On Error Resume Next
                    x = 0: x = .GroupItems.Count                'try to count the number of groupitems
                    On Error GoTo 0

                    If x > 0 Then                               'if x>0 then it's  a group
                         For i2 = 1 To .GroupItems.Count        'loop through the groupitems
                              Set gi = .GroupItems(i2) 'such a groupitem
                              On Error Resume Next
                              s = "": s = gi.Name 'try to get the name (not always successfull)
                              On Error GoTo 0
                              dict.Add dict.Count, Array(dict.Count, .Name, s)
                              If s <> "" Then
                                   If StrComp(.GroupItems(i2).Name, myname, vbTextCompare) = 0 Then     'is this the wanted shape
                                        .GroupItems(i2).Fill.ForeColor.RGB = RGB(209, 0, 250)     'change a property
                                   End If
                              End If
                         Next
                    Else                                        'not in a group
                         dict.Add dict.Count, Array(dict.Count, .Name, "no group")
                         If StrComp(.Name, myname, vbTextCompare) = 0 Then     'is this the wanted shape
                              .Fill.ForeColor.RGB = RGB(209, 0, 250)     'change a property
                         End If
                    End If
               End With
          Next
          .Range("AA1").Resize(dict.Count, 3).Value = Application.Index(dict.items, 0, 0)
     End With
End Sub
 
Upvote 0
Domenic, unfortunately, even with that obvious correction, the result was the same. Error.
In addition to correcting the textbox name, did you also amend that line of code, as per my suggestion?

And are you getting the same error, or are you getting a different one? If the latter, which error are you getting?

And when the error occurs, move your cursor over the variable sbtn. What value has it been assigned?
 
Upvote 0
Hi Domenic,

Code:
In addition to correcting the textbox name, did you also amend that line of code, as per my suggestion?
Yes. I think, but my eyes have been known to be quite inaccurate.
Code:
.Shapes("srv_tb_" & sbtn).Font.Color = RGB(209, 239, 250)

And are you getting the same error, or are you getting a different one? If the latter, which error are you getting?
Different error: "Object doesn't support this property or method."

And when the error occurs, move your cursor over the variable sbtn. What value has it been assigned?
sbtn = 1
 
Upvote 0
Yes. I think, but my eyes have been known to be quite inaccurate.
Code:
.Shapes("srv_tb_" & sbtn).Font.Color = RGB(209, 239, 250)
I see that you corrected the name, but you didn't amend the rest of that line as per my suggestion. So it should be . . .

VBA Code:
.Shapes("srv_tb_" & sbtn).TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(209, 239, 250)
 
Upvote 0
Solution
I see that you corrected the name, but you didn't amend the rest of that line as per my suggestion. So it should be . . .

VBA Code:
.Shapes("srv_tb_" & sbtn).TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(209, 239, 250)
Winner winner chicken dinner!!
Thank you Domenic for pointing out my mistakes (and correcting them). There are days where detail just eludes me.
And Bsalve, thank you to you as well. You clearly put in a lot of effort in finding roundabout ways to diagnose to solve a problem. Turns out it wasn't that complicated anyway!
 
Upvote 0

Forum statistics

Threads
1,224,827
Messages
6,181,200
Members
453,022
Latest member
RobertV1609

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top