Hi there, and welcome to the forum.
Just as a heads up, if you click "Go Advanced", you can upload your workbook and images to my site, so you don't have to link elsewhere.
With regards to your issue... the following code will do what you're asking but you do need to:
- Expand your list a bit. Each "Home" record will need it's own line.
- Make your entry match the shape name before you start changing it. i.e. if you create a new shape and it's called "Square1", you need to put "Square" in the shape column, then "1" in the Number column, then change what you're after.
Here's the code:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim shp As Shape
Dim nw As Variant
Dim old As Variant
' in case there is an error...
On Error GoTo CleanUp
Set rng = Intersect(Target.EntireRow, [Shape:Number])
Debug.Print rng.Address
If Not rng Is Nothing Then
' Save new name
nw = rng.Cells(1, 1) & "|" & rng.Cells(1, 2)
' Prevent events firing while we change sheet values
Application.EnableEvents = False
' Get previous name
Application.Undo
old = rng.Cells(1, 1) & rng.Cells(1, 2)
' Restore new name
rng.Value = Split(nw, "|")
' Rename selected Shapes
For Each shp In Me.Shapes
Debug.Print shp.Name
If shp.Name = old Then
shp.Name = Replace(nw, "|", "") 'nw & Mid(shp.Name, Len(old) + 1)
Exit For
End If
Next
End If
CleanUp:
' Re-enable events
Application.EnableEvents = True
End Sub
PS.. Congratulations, you're post number 500 in the forum.
Bookmarks