code to require 4-digits

KDS14589

Board Regular
Joined
Jan 10, 2019
Messages
149
Office Version
  1. 2016
Platform
  1. Windows
I have two codes for the same textbox. One which limits the user input to numbers only. (I'm not sure I need it with the other code). My problem is to wish to limit intake to numbers between 0000 and 9999. A four-digit number is required. My coding problem is with value <=0.1, if I change it to 0 or 1 the form accepts just a single digit. If I change it to 0000 or 0001 the code removes the leading 0's. I've tried formats and masks with no luck.

Any suggestions….


VBA Code:
Private Sub txtC_account_AfterUpdate()
  If Not IsNumeric(Me.txtC_account.Value) Then
    MsgBox "Please enter a 4-digit number from 0001 to 9999", vbCritical, "Account Number"
    Cancel = True
  ElseIf Me.txtC_account.Value <= 0.1 Or Me.txtC_account.Value > 10000 Then
    MsgBox "Please enter a 4-digit number from 0001 to 9999", vbCritical, "Account Number"
    Cancel = True
  End If
    If txtC_account.Value > 10000 And txtC_account.Value <= 0.1 Then
    MsgBox "Account number not between 0001 and 9999", vbCritical, "Account Number"
  End If
End Sub

Private Sub txtC_account_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    If (KeyAscii >= 48 And KeyAscii <= 57) Then
    KeyAscii = KeyAscii
    Else
    KeyAscii = 0
    End If

End Sub
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,611
Office Version
  1. 365
Platform
  1. Windows
See if you can make use of this, in testing it appears to trap anything that does not convert to a valid integer and in the range of 0001 to 9999.

VBA Code:
On Error Resume Next
With Me.txtC_account
    If Len(.Value) <> 4 Or CLng(.Value) = 0 Or .Value <> Format(CLng(.Value), "0000") Then MsgBox "error"
End With
On Error GoTo 0
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
7,044
Office Version
  1. 2019
Platform
  1. Windows
You can limit the Max char length in your textbox

Not sure fully understood your requirement but see if these changes to your codes help

VBA Code:
Private Sub txtC_account_Enter()
    Me.txtC_account.MaxLength = 4
End Sub

Private Sub txtC_account_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Dim entry       As Integer
    If Not Me.Visible Then Exit Sub
    With Me.txtC_account
        .Value = Format(Val(.Value), "0000")
        entry = Val(.Value)
    End With
    If entry = 0 Then
        MsgBox "Please enter a 4-digit number from 0001 To 9999", vbCritical, "Account Number"
        Cancel = True
    End If
End Sub

Private Sub txtC_account_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    If (KeyAscii >= 48 And KeyAscii <= 57) Then
        KeyAscii = KeyAscii
    Else
        KeyAscii = 0
        MsgBox "Please enter a 4-digit number from 0001 To 9999", vbCritical, "Account Number"
    End If
End Sub

adjust codes as required

Dave
 
Solution

KDS14589

Board Regular
Joined
Jan 10, 2019
Messages
149
Office Version
  1. 2016
Platform
  1. Windows
You can limit the Max char length in your textbox

Not sure fully understood your requirement but see if these changes to your codes help

VBA Code:
Private Sub txtC_account_Enter()
    Me.txtC_account.MaxLength = 4
End Sub

Private Sub txtC_account_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Dim entry       As Integer
    If Not Me.Visible Then Exit Sub
    With Me.txtC_account
        .Value = Format(Val(.Value), "0000")
        entry = Val(.Value)
    End With
    If entry = 0 Then
        MsgBox "Please enter a 4-digit number from 0001 To 9999", vbCritical, "Account Number"
        Cancel = True
    End If
End Sub

Private Sub txtC_account_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    If (KeyAscii >= 48 And KeyAscii <= 57) Then
        KeyAscii = KeyAscii
    Else
        KeyAscii = 0
        MsgBox "Please enter a 4-digit number from 0001 To 9999", vbCritical, "Account Number"
    End If
End Sub

adjust codes as required

Dave
it works THANKS
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,845
Messages
5,766,751
Members
425,378
Latest member
kapoor2892

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top