New to VBA, im trying to put specific TextBoxes into a array and then for that array have conditional formatting where as you type numbers in (In this case, Money) it will apply a comma. so instead of 7000, it will type as 7,000. Any help?
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
Basic "control array" example:
Add a class module named 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
In your userform:
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