代码之家 › 专栏 › 技术社区 › NoFafo

将文本框放入数组进行格式化

vba excel

NoFafo · 技术社区 · 4 月前

VBA的新手,我试图将特定的文本框放入一个数组中,然后对该数组进行条件格式设置,当你在其中键入数字时(在本例中为Money),它将应用逗号。因此,它将键入7000而不是7000。有什么帮助吗?

Private Sub FormatTextBoxes()
    Dim i As Variant
    Dim iArray As Variant
    Dim txtBox As Object
    
    iArray = Array(2, 7, 50, 51, 55, 62, 63, 64, 67, 69, 71, 78, 86, 91, 92, 94, 95, 102, 103, 107, 108, 111)
    For Each i In iArray
        On Error Resume Next ' Add error handling
        Set txtBox = Me.Controls("TextBox" & i)
        
        If Not txtBox Is Nothing Then ' Check if the textbox exists
            If IsNumeric(txtBox.Text) And txtBox.Text <> "" Then
                txtBox.Text = Format(Abs(CDbl(txtBox.Text)), "#,###")
            End If
        End If
        On Error GoTo 0 ' Reset error handling
    Next i
End Sub

1 回复 | 直到 4 月前

Tim Williams 4 月前

基本的“控制阵列”示例:

添加一个名为的类模块 clsTxtbox :

Option Explicit

Public WithEvents tb As MSForms.TextBox

Private Sub tb_Change()
    If IsNumeric(tb.Text) And tb.Text <> "" Then
        tb.Text = Format(Abs(CDbl(tb.Text)), "#,###")
        Debug.Print tb.Text
    End If
End Sub

在您的用户表单中:

Dim colTB As Collection 'for storing the event-handling objects

Private Sub UserForm_Activate()
    SetupTextBoxes  'initialize event capture
End Sub

Private Sub SetupTextBoxes()
    Dim i As Variant
    Dim iArray As Variant
    Dim txtBox As Object
    
    Set colTB = New Collection
    
    iArray = Array(2, 7, 50, 51, 55, 62, 63, 64, 67, 69, 71, 78, _
                   86, 91, 92, 94, 95, 102, 103, 107, 108, 111)
    For Each i In iArray
        Set txtBox = Nothing
        On Error Resume Next ' Add error handling
        Set txtBox = Me.Controls("TextBox" & i)
        On Error GoTo 0 ' Reset error handling
        
        ' Check if the textbox exists: if Yes then set up Change event
        If Not txtBox Is Nothing Then
            Debug.Print "Found textbox: " & txtBox.Name
            colTB.Add EvtObj(txtBox)  'set up the event capture
        End If
    Next i
End Sub

'create, configure and return an instance of `clsTxtBox`
Function EvtObj(tbox As Object) As clsTxtBox
    Set EvtObj = New clsTxtBox
    Set EvtObj.tb = tbox
End Function

推荐文章