VBA loop in change event error

Question

I have this macro that creates a textbox in worksheet 2 when I write something in cell A1 of worksheet 1, and when I delete that value it deletes the textbox. I want to do that for several cells, but it just is working. If Cell A1 has a value a textbox with that value should appear, if the A2 has a value a textbox with that value should appear, but if I delete A1 it should delete the texbox that refers to A1, not all of the textboxes

Sub RemoveShapes()

    Dim shp As Shape
    For Each shp In Worksheets(2).Shapes
        If shp.Type = msoTextBox Then shp.Delete
    Next shp

End Sub

Sub criarcaixastexto()

    Dim wsActive As Worksheet
    Dim box As Shape

    Set wsActive = Worksheets(2)
    Set box = wsActive.Shapes.AddTextbox(msoTextOrientationHorizontal, 20, 20, 100, 50)

    box.TextFrame.Characters.Text = Range("Folha1!A1").value

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
        Call criarcaixastexto
    End If
End Sub

I tried this but it doesn't work

'macro para apagar
Sub removercaixas()

    Dim shp As Shape
    For Each shp In Worksheets(2).Shapes
        If shp.Type = msoTextBox Then shp.Delete
    Next shp

End Sub

'macro para criar caixas de texto
Sub criarcaixastexto()

   Dim wsActive As Worksheet
   Dim box As Shape

   Set wsActive = Worksheets(2)
   Set box = wsActive.Shapes.AddTextbox(msoTextOrientationHorizontal, 20, 20, 100, 50)

   box.TextFrame.Characters.Text = Worksheets(1).Cells(i, 1).Value

End Sub

' macro corre ao escrever texto numa célula
Private Sub Worksheet_Change(ByVal Target As Range)

    For i = 1 To 3
        If Target.Count > 1 Then Exit Sub
        If Not Intersect(Target, Range("A&i")) Is Nothing Then
            removercaixas
        If Len(Target) > 1 Then criarcaixastexto
        End If
    Next

End Sub

Please edit your post to describe exactly what the error is. "doesn't work" is not a useful problem description. — Mathieu Guindon
– Mathieu Guindon, Commented Oct 19, 2017 at 17:17
removercaixas removes all textbox shapes. you need to use shp.TopLeftCell.Address to determine the location. — Sorceri
– Sorceri, Commented Oct 19, 2017 at 20:02

MoondogsMaDawg · Accepted Answer · 2017-10-23 13:44:54Z

1

You are removing all textboxes on the sheet any time you call removercaixas. You need to somehow link the textbox with the cell it was generated by.

Why not name the textbox with the cell address? Copy/Paste this:

Sub removercaixas(strName As String)

    Dim shp As Shape
    For Each shp In Worksheets(2).Shapes
        If shp.Type = msoTextBox AND shp.Name = strName Then shp.Delete
    Next shp

End Sub

And

Sub criarcaixastexto(strName As String)

    Dim wsActive As Worksheet
    Dim box As Shape

    Set wsActive = Worksheets(2)
    Set box = wsActive.Shapes.AddTextbox(msoTextOrientationHorizontal, 20, 20, 100, 50)

    box.TextFrame.Characters.Text = Worksheets(1).Range(strName).Value
    box.Name = strName

End Sub

And

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    Select Case Target.Address
        Case "$A$1", "$A$2", "$A$3"
            removercaixas (Target.Address)
        Case Else
            Exit Sub
    End Select
    If Len(Target) > 1 Then criarcaixastexto (Target.Address)
End Sub

Textboxes are created in worksheet 2 all on top of each other. They are deleted appropriately. No textbox is created when the value entered in $A$1:$A$3 has a length of 1 or less. Not sure what the logic is there, but if you want single digit values to create a textbox just change the Len(Target) > 1 to Len(Target) > 0.

edited Oct 23, 2017 at 13:44

answered Oct 19, 2017 at 20:02

MoondogsMaDawg

1,71413 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

user7409049 Over a year ago

I tried your code with this but it dind't work. I get the following error: Run-time 1004. Method Range of objet Worksheet failed.

Private Sub Worksheet_Change(ByVal Target As Range)  For i = 1 To 3         If Target.Count > 1 Then Exit Sub         If Not Intersect(Target, Range("A" & CStr(i))) Is Nothing Then             removercaixas (Target.Address)         If Len(Target.Address) > 1 Then criarcaixastexto (Target.Address)         End If Next  End Sub

MoondogsMaDawg Over a year ago

There are two additional changes to your code: the 'len' and 'intersect' arguments. Are you sure those aren't the culprits? I'm honestly confused about what that part of the code is doing.

MoondogsMaDawg Over a year ago

Did this compile? None of my changes seem to be causing the 1004. I've fixed 2 compile errors already, and getting a 1004 on the box.TextFrame line due to i being out of scope and not defined in the sub. Can you fix all of these issues and then let me know whether my changes fix your initial problem?

user7409049 Over a year ago

Making the changes you suggest doesn't delete the old version of the textboxes when I update the values in worksheet1

user7409049 Over a year ago

So you tested this code? sorry, nothing happens in my excel with that code. I kept my code and changed the change event to: Private Sub Worksheet_Change(ByVal Target As Range) Call removercaixas Call criarcaixastexto End Sub it works like I want but will be triggered by any change in any cell of that page. I want it "lighter". What I want if closer to what you suggest. I want the texboxes to update when I change something in the correpondent cell, but it doesn't do that. if I delete cell A1 I want textbox A1 to be deleted, If I write something in A2, I want that texbox to be updated

|

Collectives™ on Stack Overflow

VBA loop in change event error

1 Answer 1

13 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

13 Comments

Your Answer

Sign up or log in

Post as a guest

Related