I have a table in a word document macro-enabled template. Each cell has an ActiveX TextBox in it. In one specific column, I only want to accept numerical characters. If a user enters anything else, a message box pops up informing the user that this is an error.
I would also like to have the non-conforming text be deleted, or the last action to be undone. Unfortunately whatever is typed into an ActiveX control is not registered in the undo table so "Undo" doesn't work. This is my code.
Brief Explanation: Cell 1 is the column title and is not changed (named
QTY). Each ActiveX TextBox is in cells 2-20 in the column and is named
txtQty#. This is where you enter in numbers only. Cell 21 is the Total value of the numbers entered in cells 2-20 and is named
txtTotalQty.
Code:
Private Sub QtyChanged(newValue As String)
If Trim(newValue) <> "" And Not IsNumeric(newValue) Then
'Display a pop-up message
MsgBox "This cell only accepts numerical values. Please make the correction before proceeding", vbOKOnly, "ERROR!"
Else
With Me
txtTotalQty.Text = Format(Val(.txtQty1.Value) + Val(.txtQty2.Value) + Val(.txtQty3.Value) + Val(.txtQty4.Value) + Val(.txtQty5.Value) + Val(.txtQty6.Value) + Val(.txtQty7.Value) + Val(.txtQty8.Value) + Val(.txtQty9.Value) + Val(.txtQty10.Value) + Val(.txtQty11.Value) + Val(.txtQty12.Value) + Val(.txtQty13.Value) + Val(.txtQty14.Value) + Val(.txtQty15.Value) + Val(.txtQty16.Value) + Val(.txtQty17.Value) + Val(.txtQty18.Value) + Val(.txtQty19.Value), "#,##0")
End With
End If
End Sub
Ideally the solution should involve the current actively selected ActiveX TextBox control being completely cleared after the user clicks "OK" on the error message to dismiss it.