Form Textbox to only allow certain values

[paldob]

Hey,

I have a textbox on my form that manually has values entered based on a Truth Table however, I want to be able to override these fixed values and then input my own if applicable.


I have an override button that activates the manual entry into these boxes however, I'm having trouble on writing code to only allow the following values to be entered: 1.1 or 1.2 or 2.1 or 2.2 or 3 or 4.

If the values are entered incorrectly, I want the value to automatically reset to "". Is this possible?

Private Sub tBox1_Change()
'affects values
 
    With Me
        'With .tBox1
            'when override only allow 1.1, 1.2, 2.1, 2.2, 3 & 4 as manual entries last term ensure code is not restricted eg code can enter N/A
            'values entered outwith the allowed range will be cleared
            If Not (.Value = "1.1" Or .Value = "1.2" Or .Value = "2.1" Or .Value = "2.2" Or .Value = "3" Or .Value = "4") And Me.Override.Value Then .Value = ""
        End With
        Call ColourCoding(.tBox1)
    End With
        
End Sub

 

[dmt32]

Hi,
welcome to Forum.

You probably will need to use BeforeUpdate event as it has the Cancel argument & will enable you to keep focus on the control if invalid entry made.


Try following:

Private Sub tBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    Dim m As Variant
    With Me.tBox1
        m = Application.Match(Val(.Value), Array(1.1, 1.2, 2.1, 2.2, 3, 4), False)
        If IsError(m) Then .Value = "": Cancel = True
    End With
End Sub

Adjust to meet your project need as required

Dave

 

[paldob]

Hi Dave,

Thank you for your input on post #2 . I have never used _BeforeUpdate, it has opened my eyes to something new.

However, I decided to go with a combo box with the values set on my truth table tab. Less chance of error on my part, until I become more comfortable with vb.

Kind regards
Paldob

Hi,
welcome to Forum.

You probably will need to use BeforeUpdate event as it has the Cancel argument & will enable you to keep focus on the control if invalid entry made.


Try following:

Private Sub tBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    Dim m As Variant
    With Me.tBox1
        m = Application.Match(Val(.Value), Array(1.1, 1.2, 2.1, 2.2, 3, 4), False)
        If IsError(m) Then .Value = "": Cancel = True
    End With
End Sub

Adjust to meet your project need as required

Dave