If/Else clear current activex textbox

gmayor · #1 01-28-2022, 10:14 PM

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

DrTyDawg · #2 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.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Event macro: insert row with formulae based on current row, clear selected contents	emsa	Excel Programming	5	04-17-2020 09:03 AM
How to track changes in a ActiveX Textbox	tommasorossotti	Word VBA	1	02-24-2020 03:57 AM
ActiveX Textbox properties using VBA	kyeung	Word VBA	5	10-05-2015 12:47 AM
Display result in textbox based on the input of another textbox	scarymovie	Word VBA	5	05-16-2012 07:05 PM
How to insert a hyperlink in activex textbox	Joe Patrick	Word VBA	1	10-03-2011 06:03 AM