Microsoft Office Forums - View Single Post - [Solved] If/Else clear current activex textbox

DrTyDawg · 02-01-2022, 09:16 AM

Quote:

Originally Posted by gmayor

You don't actually need a message box, you need a series of macros in the ThisDocument module related to the text boxes e.g. as follows. (I have only shown the first two).
If the user enters a non-numeric character (except a full stop/period) the PC will beep and cancel that character. You could use a message box instead of the beep, but it isn't required.
If you don't want to allow the full stop/period, omit the reference to '46,' in the case statement. If you want to include the thousands separator or you use a comma as a decimal point add '44, ' before the 46 i.e.

Code:

Case 44, 46, 48 To 57

Code:

Option Explicit
Private bTest As Boolean

Private Function IsAllowed(ByVal i As String) As Boolean
Select Case Val(i)
        Case 46, 48 To 57
            IsAllowed = True
        Case Else
            IsAllowed = False
    End Select
lbl_Exit:
    Exit Function
End Function

Private Sub txtQty1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    bTest = IsAllowed(CStr(KeyAscii))
    If bTest = False Then
        Beep
        KeyAscii = 0
    End If
lbl_Exit:
    Exit Sub
End Sub

Private Sub txtQty2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    bTest = IsAllowed(CStr(KeyAscii))
    If bTest = False Then
        Beep
        KeyAscii = 0
    End If
lbl_Exit:
    Exit Sub
End Sub

Thanks for the suggestions. This works well. I will likely keep the Msgbox as this will be used in an environment where a 'beep' from a computer cannot be heard.
Just a quick question for you about code cleanup/length if you know. Is there a way to accomplish what you suggested in a more condensed format? I know I only have 19 fields so it's not a big deal but what if it was 100 fields? Then there would be a lot of Private Sub's to enter.