excel - 尝试更新现有文本框，但收到 "Object Required"错误

Question

我录制了一个宏，开始更新文本框。不幸的是，如您所知，录制宏不会设置变量。
录制的宏显示:

ActiveSheet.shapes.Range(Array("TextBox 8")).Select
Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = _
  "Kroger Market 6 " & Chr(13) & "W/E P8 Week 4" & Chr(13) & "9/23/2020"

我试图将其调整为:

With wsWeeklyDivision
    Dim EndDate As String
    EndDate = Left(Right(.Range("A4"), 24), 23)
End With

With wsFront
    Dim shapes As Variant
    shapes = shapes.Range(Array("TextBox 8"))
    With shapes
        .ShapeRange(1).TextFrame2.TextRange.Characters.Text = _
        "Kroger Market 6 " & Chr(13) & "Date Range" & Chr(13) & Format(Now(), "MM.DD.YY")
    End With
End With

我什至更改了 shapes = shapes.Range(Array("TextBox 8"))至 Set shapes = shapes.Range(Array("TextBox 8")) ，但我仍然收到需要对象的错误。
请问我需要改什么？

Answer 1

使用Set进行对象分配时。
形状从来没有资格。

shapes = shapes.Range(Array("TextBox 8"))

shapes.Range(Array("TextBox 8"))返回一个 ShapeRange

With wsFront
    Dim ShapeRange As ShapeRange
    Set ShapeRange = .Shapes.Range(Array("TextBox 8"))
    
    Dim TextBox8 As Shape
    Set TextBox8 = ShapeRange.Item(1)
    
    With TextBox8.TextFrame2.TextRange.Characters
        .Text = "Kroger Market 6 " & Chr(13) & "Date Range" & Chr(13) & Format(Now(), "MM.DD.YY")
    End With
End With

只需要在对 Shape 进行分组时使用 ShapeRange。

With wsFront
    Dim TextBox8 As Shape
    Set TextBox8 = .Shapes("TextBox 8")
    
    With TextBox8.TextFrame2.TextRange.Characters
        .Text = "Kroger Market 6 " & Chr(13) & "Date Range" & Chr(13) & Format(Now(), "MM.DD.YY")
    End With
End With

用正确的类型声明变量可以更容易编码。您可以使用 TypeName 返回一个 Objects 类型。

确保在使用 ClassName 或标准属性名称时匹配大小写。吨
变暗的形状