VBA Userform invalid procedure textbox

Question

I have the following script that allows only integers up to 31, lenght of the string not more than 2 and has to be a numeric value.

If that is not met i want to delete the last inserted value(so that the user is not able to insert any of these inputs).

Private Sub txtDay_Change()

If Me.txtDay.Value > 31 Or Len(Me.txtDay.Value) > 2 Or Not IsNumeric(Me.txtDay.Value) Then
    
    Me.txtDay.Value = Left(Me.txtDay.Value, Len(Me.txtDay.Value) - 1)
    Cancel = True
    
    Else
    Sheets("do not open!").Range("C1") = Me.txtDay.Value


End If

The issue is that if I insert a text or hit backspace then i get the following error:

What I did to resolve this is by applying an On error GoTo

Private Sub txtDay_Change()

On Error GoTo er


If Me.txtDay.Value > 31 Or Len(Me.txtDay.Value) > 2 Or Not IsNumeric(Me.txtDay.Value) Then
    
    
    Me.txtDay.Value = Left(Me.txtDay.Value, Len(Me.txtDay.Value) - 1)
    Cancel = True
    
    Else
    Sheets("do not open!").Range("C1") = Me.txtDay.Value


End If

er:
Cancel = True

It seems to work find now. but would anyone know if this would be a good approach? and would anyone know why this is happening ?

Your Left function will fail with that error if the textbox length is less than 2 which is possible for 2 of your tests. — Rory
– Rory, Commented Dec 8, 2022 at 9:03

Ike · Accepted Answer · 2022-12-08 08:54:39Z

0

You have to run the isNumeric-check first - otherwise it could happen, that you compare a string to a number - which fails.

I would use an extra function for this:

Private Function isDayValueOK(value As Variant) As Boolean

If Not IsNumeric(value) Then Exit Function
'If Len(value) > 2 Then Exit Function 'you don't need this because then the number is greater than 100 and greater than 31.
If value > 31 Then Exit Function

isValueOK = True

End Function

You can then call this function like this: If isDayValueOK(Me.txtDay.Value) = false then ...

answered Dec 8, 2022 at 8:54

Ike

13.4k6 gold badges20 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA Userform invalid procedure textbox

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related