vba - 如何检查文本框中的数据

Question

我有一个名为“输入”的工作表，带有一个按钮 (ActiveX) 和一个文本框 (ActiveX)。当用户单击按钮时，我使用 VBA 检查文本框的值，但代码找不到文本框。

我的按钮代码:

Sub Toevoegen()
Dim invoerenws As Worksheet
Dim overzichtws As Worksheet

Dim nextRow As Long
Dim oCol As Long

Dim myRng As Range
Dim myCopy As String
Dim myCell As Range

'cells to copy from Input sheet - some contain formulas
myCopy = "TextBox1"

Set invoerenws = Worksheets("invoeren")
Set overzichtws = Worksheets("overzicht")

With overzichtws
    nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With

With invoerenws
    Set myRng = .Range(myCopy)

    If Application.CountA(myRng) <> myRng.Cells.Count Then
        MsgBox "Please fill in all the cells!"
        Exit Sub
    End If
End With

With overzichtws
    With .Cells(nextRow, "A")
        .Value = Now
        .NumberFormat = "mm/dd/yyyy hh:mm:ss"
    End With
    .Cells(nextRow, "B").Value = Application.UserName
    oCol = 3
    For Each myCell In myRng.Cells
        overzichtws.Cells(nextRow, oCol).Value = myCell.Value
        oCol = oCol + 1
    Next myCell
End With

'clear input cells that contain constants
With invoerenws
  On Error Resume Next
     With .Range(myCopy).Cells.SpecialCells(xlCellTypeConstants)
          .ClearContents
          Application.GoTo .Cells(1) ', Scroll:=True
     End With
  On Error GoTo 0
End With
End Sub

Answer 1

如果值仍在文本框内，则必须引用对象或控件。你可以这样做:

textBox.value = worksheet.cells(1,1)

这将显示 textBox 的值。表单控件到单元格 A1。 我知道这不是您要寻找的，但这是向您展示如何引用用户窗体对象或控件的最简单方法。您可以在如下窗口中找到文本框控件的名称:



您需要使用 (姓名) 引用文本框时