Shape selection based on user input

[640k]

Hi - having trouble finding an exact answer to my problem.

Trying to select named shapes based on interaction from user within a table. Basically the user selects a country, the country changes color. This works well when I specifically call out each individual name:

ActiveSheet.Shapes.Range(Array([NorthAmerica])).Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent2

but when I try to involve the target, i'm having trouble...

    For Each strRNG In Target


        Debug.Print ActiveSheet.Shapes.Range(Array([strRNG])).Name
        
    Next strRNG

I have no idea what I'm missing to get this to work. I've tried a few different approaches but need a fresh set of eyes.

Thanks.

[SamT]

I can't tell if strRng is a Range or a String.

I can't tell if the User is Selecting 1 cell or several.

This code, (which might not work,) is based on the User only Selecting one cell at a time.

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

  Dim SelectionRange As Range
  Set SelectionRange = Range("A:A")  'assign as needed
  
  If Target.Count <> 1 Then Exit Sub
  
  If Not Intersect(Target, SelectionRange) Is Nothing Then
    ActiveSheet.Shapes.Range(Target.Text).Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent2
  End If
  
End Sub

[640k]

I can't tell if strRng is a Range or a String.

I can't tell if the User is Selecting 1 cell or several.

This code, (which might not work,) is based on the User only Selecting one cell at a time.

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

  Dim SelectionRange As Range
  Set SelectionRange = Range("A:A")  'assign as needed
  
  If Target.Count <> 1 Then Exit Sub
  
  If Not Intersect(Target, SelectionRange) Is Nothing Then
    ActiveSheet.Shapes.Range(Target.Text).Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent2
  End If
  
End Sub

i'm using strRNG to comb through possible multiple selections based on Target intersection (so if user selects multiple countries in the table, they'll all highlight). as I've said previously, if I specifically call out each individual country, the code works fine. shapes.range is not compatible with any form of strRNG.Value/.Text or Target.Value/.Text.

In this example, UK/IRELAND work fine, but TURKEY does not:

        Select Case strRNG
            Case "UK/IRELAND"
                ActiveSheet.Shapes.Range(Array([Britain], [NorthernIreland], [Ireland])).Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent2
            Case "TURKEY"
                ActiveSheet.Shapes.Range(strRNG.Text).Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent2
        End Select

The error is always "item" or "object" not found or defined. the code isn't working to translate the text into a working named range. the shapes are FreeForm either individual "FreeForm29" or groups "Group 1". I feel like if I can just figure out how to take the strRNG.Value (or Target.Value) and produce the "Refers To" from the Name Manager I can get this to work.

[640k]

BTW even

Debug.Print ActiveWorkbook.Names(strRNG.Text).RefersTo

gives me an error whereas

Debug.Print ActiveWorkbook.Names("NorthAmerica").RefersTo

gives me the actual freeform object reference.


EDIT:

OK so I declared strRNG as a Variant and for whatever reason I'm now getting the .RefersTo statement to work:

Debug.Print ActiveWorkbook.Names(strRNG.Text).RefersTo

however, this line still doesn't work:

ActiveSheet.Shapes.Range(ActiveWorkbook.Names(strRNG.Text).RefersTo).Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent2

still troubleshooting.

[640k]

So holy cow I finally got it. Gee that took way longer than needed. lol.

So the .RefersTo is exactly what I needed, but I had to strip out the ="" from the text and then assigned to a variable. not sure if there's a cleaner way, but this worked well for me:

strObject = Replace(Replace(ActiveWorkbook.Names(UCase(Replace(strRNG.Text, " ", ""))).RefersTo, """", ""), "=", "")
ActiveSheet.Shapes.Range(Array(strObject)).Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent2

[SamT]

I am glad you found the answer.

Why are you prefixing different Types of variables with the prefix that indicates a variable of Type String?

I see a variable named "Rng" that is a Object Type variable
I see a variable named "Rng" that is a Variant Type variable
I see a variable named "Object" that is a String Type variable
All three variables carry the prefix that indicates the String Type

It is extremely confusing to other programmers. It almost guarantees that we won't understand your code.



Looking at the Name Manager image (I don't have Name Manager) you might try this

ActiveSheet.Shapes.Range(strRNG.Name.Name).Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent2

Where strRNG is a Range Object:
strRNG.Name is a Defined Name Object (Worksheet.Names("Greece"))
strRNG.Name.Name is a String ("Greece")
strRNG.Name.Value is the RefersTo string ("=Freeform10")



What I really don't understand is why you didn't give the FreeForms Names like "ffGreece" and "ffTurkey"? Then this would work

For Each Cel in Target
ActiveSheet.Shapes.Range("ff" & Cel.Text).Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent2

[640k]

I am glad you found the answer.

Why are you prefixing different Types of variables with the prefix that indicates a variable of Type String?

I see a variable named "Rng" that is a Object Type variable
I see a variable named "Rng" that is a Variant Type variable
I see a variable named "Object" that is a String Type variable
All three variables carry the prefix that indicates the String Type

It is extremely confusing to other programmers. It almost guarantees that we won't understand your code.

was in a rush. just vomited some text on the screen to get it going.

Looking at the Name Manager image (I don't have Name Manager) you might try this

ActiveSheet.Shapes.Range(strRNG.Name.Name).Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent2

Where strRNG is a Range Object:
strRNG.Name is a Defined Name Object (Worksheet.Names("Greece"))
strRNG.Name.Name is a String ("Greece")
strRNG.Name.Value is the RefersTo string ("=Freeform10")

this doesn't work because of my issues outlined above. Shapes.Range seems to be treating the text as text and not as a named range. therefore every iteration i tried failed. it wasn't until i got the RefersTo value that this worked as desired.

What I really don't understand is why you didn't give the FreeForms Names like "ffGreece" and "ffTurkey"? Then this would work

For Each Cel in Target
ActiveSheet.Shapes.Range("ff" & Cel.Text).Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent2

i'm not sure this would work any better. can you explain what difference the "ff" proceeding the name has on the impact of calling shapes.range against a text value? i feel as if I'd run in to the earlier issues.

Thanks for everyone's input!

[SamT]

In Shapes.Range(Index), Index can be a number or a string.

If the Index of the Range is a String, it must be the name of a ShapeRange Object which is a Shapes Collection My bad, I missed this before.

With the Shapes Collection. one uses the Item Method, (Default of the Shapes collection,) thusly Shapes(index) where Index is the Name of the Shape to return a particular Shape.

I now think that you wanted

'Assumes that each Shape's name is stored in a cell in Target.
For Each strRng in Target
ActiveSheet.Shapes(strRng).Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent2.

The ff prefix I used above on the shape name is merely to differentiate the Shape name from the Cell Value. You don't really need it. Since all your shapes are about countries, you could as easily use "nt" or "na" for Nation,. or "co" for country, or add an underscore for readability, ("shp_Greece").

In truth, since the only two places in your project where a country name will be located is as a Cell.value and as a Shape.Name, I would not use a prefix.

There will be times when you need to have several Variables with the same name. For example, the name of an input control on a UserForm, ("ioFirstName",) the Name of a Column (range), ("empFirstName",) on a Worksheet, ("Employees",) and the name of a Variable, ("FirstName",) to temporarily hold the person's first name. I can use that same UserForm with Worksheets("Clients") and columns("cliFirstName".)

[640k]

In Shapes.Range(Index), Index can be a number or a string.

If the Index of the Range is a String, it must be the name of a ShapeRange Object which is a Shapes Collection My bad, I missed this before.

With the Shapes Collection. one uses the Item Method, (Default of the Shapes collection,) thusly Shapes(index) where Index is the Name of the Shape to return a particular Shape.

I now think that you wanted

'Assumes that each Shape's name is stored in a cell in Target.
For Each strRng in Target
ActiveSheet.Shapes(strRng).Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent2.

I tried your above method but it gave me an error, so I'm not seeing this as a possibility. Fill is not a member of the Shapes object. You have to get to .Item or .Range. Since .Range allows me to select one or multiple shapes, I still think .Range is the best method.

[SamT]