PDA

View Full Version : Help with macro to update text on shapes



dave1414
08-02-2012, 01:29 PM
Hi All,
I need some help/direction with something I'm trying to accomplish. I have an excel workbook with two tabs: 1 tab is a list with the first column containing a number for each row, i.e. 1,2,3,4, etc. The 2nd tab is a drawing, on which I have placed shapes (via Insert > oval). I want to number the shapes (i.e. text box on the shape). I can do all this manually, but ideally I would like the number on the shape in the one tab to be linked to the number in column A in the first tab. In other words, if I change the numbers for each of the rows in column A, it would automatically update the text displayed on the corresponding shape to the new number.

Any thoughts/comments on whether this is doable would be greatly appreciated!

Bob Phillips
08-02-2012, 02:02 PM
Add this to the first sheet code module

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:A10"

On Error GoTo ws_exit

Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

With Target

Worksheets("Sheet2").Shapes("Oval " & .Row).TextFrame2.TextRange.Characters.Text = .Value
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

dave1414
08-03-2012, 05:52 AM
Thanks for the reply. It doesn't seem to be working. I've uploaded a sample file to explain what I'm try to achieve. Sheet 2 contains a map with numbered labels (oval + text). The numbers on the labels correspond to a unique number for each row (Column A) of the list in Sheet 1.

What I would like would be to link the number in Column A to the text appearing on each oval, so that if I insert a row in Sheet 1, say between Vancouver and Edmonton, that the numbers on the ovals would update to reflect the new numbering in Sheet 1.

I'm also wondering whether I can achieve this using hyperlinks, but I haven't figured out how to link the text appearing on the oval in Sheet 2, with the number appearing in Column A in Sheet 1.