vba - 刷新文本框VBA

Question

我想要一个宏，当我在 Worksheet1!A1 中写入内容时，它会在 Worksheet2 中创建一个文本框。问题是我希望每当刷新数据时它都会刷新。我做了一个，但再次运行宏，所以我留下了几个文本框，一个在其他文本框之上。如果单元格为空，我还想删除文本框。

如果有任何帮助，我将不胜感激。谢谢。这是我的代码:

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

Answer 1

要忽略空值，请将事件更改为以下值:

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

这将在写入新形状之前删除形状。

Sub RemoveShapes()

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

End Sub