refresh a textbox VBA

Question

I want a macro that creates a textbox in Worksheet2 when I write something in Worksheet1!A1. The problem is that I want it to refresh whenever I refresh the data. I made one but is runs the macro again, so I am left with several textbox, one on top of the others. Also I want to delete the textbox if the cell is empty.

I would appreciate any help. Thanks. Here is my code:

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

Instead of creating newtextboxes, you could have one TextBox created and then just change the value of that TextBox when cell A1 is changed. — danieltakeshi
– danieltakeshi, Commented Oct 18, 2017 at 11:09
@danieltakeshi - its a nice idea, but the OP wants to delete it when the value is empty. — Vityata
– Vityata, Commented Oct 18, 2017 at 11:20
Yeah, I just gave a tip, because i think that changing the TextBox value is simpler and has a better time performance. — danieltakeshi
– danieltakeshi, Commented Oct 18, 2017 at 11:33

Vityata · Accepted Answer · 2017-10-18 12:16:21Z

2

To ignore empty values change the event to this one:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub 'to avoid multiple selection.
    If Target.Address = "$A$1" Then
        RemoveShapes
        If Len(Target) > 1 then Criarcaixastexto
    End If
End Sub

This will remove the shapes, before writing new ones.

Sub RemoveShapes()

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

End Sub

edited Oct 18, 2017 at 12:16

answered Oct 18, 2017 at 11:13

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

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

2 Comments

user7409049 Over a year ago

Thank you so much for the help, but when I delete the value in A1, the shape is not deleted. Is that possible? It does delete the old ones but if the cell is empty I still have the textbox

user7409049 Over a year ago

now I am trying to expand my code to more lines and I am getting a problem with the last macro again.

Sub criarcaixastexto()     Dim wsActive As Worksheet    Dim box As Shape  For i = 1 To 3        Set wsActive = Worksheets(2)        Set box = wsActive.Shapes.AddTextbox(msoTextOrientationHorizontal, 20, 20, 100, 50)     box.TextFrame.Characters.Text = Worksheets(1).Cells(i, 1).Value    Next      End Sub

so i changed this one and it works but now the one that starts the macro just by imputing nrs doesn't. Can you help me out?

Collectives™ on Stack Overflow

refresh a textbox VBA

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related