Active Control

[gdspeare]

I am trying to create a routine that I can call into any textbox_change event, but my code isn't working. I will be calling this routine on 12 text boxes that sit within 12 different frames....anyone see why this isn't working?



Private Sub test()
With Me.ActiveControl
If Not IsNumeric(.Value) And .Value <> vbNullString Then
MsgBox "Sorry, only numbers allowed"
.Value = vbNullString
End If
End With

End Sub

 

[sunnyland]

Hello there,

Rather than putting your code on the change event try to place it on the beforeupdate events as follow passing as reference to the sub the textbox control:

Private Sub test(texti As Control)
If TypeOf texti Is MSForms.TextBox Then
With texti
If Not IsNumeric(.Value) And .Value <> vbNullString Then
MsgBox "Sorry, only numbers allowed"
.Value = vbNullString
End If
End With
End If
End Sub

Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
test TextBox1
End Sub

Private Sub TextBox2_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
test TextBox2
End Sub

Private Sub TextBox3_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
test TextBox3
End Sub

As with your code when you put textbox in a frame the activecontrol will be the frame.