Excel VBA - Color the shapes when shape is selected

[jalmbris]

Hi all ..

I have tried to find answers to this but have not found anything that has solved it for me

sh_name = w.Shapes(Application.Caller).Name <<<< ---- This one gives the error

The object with the specified name could not be found
but it should find the names

Sub ChangeShapeColor()

Dim sh_name As Variant
Dim w As Worksheet
Dim sp As Shape

With Application
.ScreenUpdating = 0
Set w = Worksheets("Dash")
sh_name = w.Shapes(Application.Caller).Name  ' <<<<< ---- this one gives the error
For Each sp In w.Shapes
w.Shapes.Range(sp.Name).Select
If sp.Name <> sh_name Then
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(248, 248, 248)
With Selection.ShapeRange.TextFrame2.TextRange.Font
    .Fill.ForeColor.RGB = rgbBlack
    .Bold = msoFalse
End With
With Selection.ShapeRange.Line
    .ForeColor.RGB = rgbBlack
    .Weight = 1.2
End With

Else:

Selection.ShapeRange.Fill.ForeColor.RGB = RGB(214, 254, 224)
With Selection.ShapeRange.TextFrame2.TextRange.Font
    .Fill.ForeColor.RGB = vbBlack
'    .Bold = msoCTrue
End With
With Selection.ShapeRange.Line
    .ForeColor.RGB = vbBlack
    .Weight = 1.2
End With

End If
Next sp
[A1].Select
.ScreenUpdating = -1
End With

Blad1.Range("A1:CK200").Interior.Color = RGB(255, 255, 255)

End Sub

 

[Georgiboy]

Have you assigned the above macro to the shape(s) to be changed, it won't work unless this macro is assigned to the shape(s) that is/ are being clicked.

It works on my end with three shapes, it makes the shape being clicked turn green and the other shapes go a light grey colour.

 

[jalmbris]

Hi Georgiboy,

when I try to run the code in excel, I get the following error when the code is in Sheet1 "image: Shapes-error.JPG"
and when the code is in Module "siteButtons" I get the following error "image: Shapes-error2.JPG" but the buttons change color but the error message comes up

Regards
jalmbris

 

[jalmbris]

Hi,

Now I know what the error is due to..
I also have hidden shapes that should be shown when you have clicked on a certain shape,
and it is these hidden shapes that cause errors.

The question is how to change the code so that it also handles these hidden shapes.

Regards
Jalmbris

 

[Georgiboy]

Maybe swap this bit:

For Each sp In w.Shapes
w.Shapes.Range(sp.Name).Select
If sp.Name <> sh_name Then
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(248, 248, 248)
With Selection.ShapeRange.TextFrame2.TextRange.Font
    .Fill.ForeColor.RGB = rgbBlack
    .Bold = msoFalse
End With
With Selection.ShapeRange.Line
    .ForeColor.RGB = rgbBlack
    .Weight = 1.2
End With

Else:

Selection.ShapeRange.Fill.ForeColor.RGB = RGB(214, 254, 224)
With Selection.ShapeRange.TextFrame2.TextRange.Font
    .Fill.ForeColor.RGB = vbBlack
'    .Bold = msoCTrue
End With
With Selection.ShapeRange.Line
    .ForeColor.RGB = vbBlack
    .Weight = 1.2
End With

End If
Next sp

For this:

For Each sp In w.Shapes
    If sp.Visible Then
        w.Shapes.Range(sp.Name).Select
        If sp.Name <> sh_name Then
            Selection.ShapeRange.Fill.ForeColor.RGB = RGB(248, 248, 248)
            With Selection.ShapeRange.TextFrame2.TextRange.Font
                .Fill.ForeColor.RGB = rgbBlack
                .Bold = msoFalse
            End With
            With Selection.ShapeRange.Line
                .ForeColor.RGB = rgbBlack
                .Weight = 1.2
            End With
        Else
            Selection.ShapeRange.Fill.ForeColor.RGB = RGB(214, 254, 224)
            With Selection.ShapeRange.TextFrame2.TextRange.Font
                .Fill.ForeColor.RGB = vbBlack
            '    .Bold = msoCTrue
            End With
            With Selection.ShapeRange.Line
                .ForeColor.RGB = vbBlack
                .Weight = 1.2
            End With
        End If
    End If
Next sp

 

[jalmbris]

Yes!
Thanks Georgiboy, that's was it ..

Regards
Jalmbris