Checking if any text has been entered

[Pacman52]

Hi all, could someone please let me know where I'm going wrong with the code below?

What I'm trying to achieve is the textbox 'textboxDay1Miles' should only accept number values, if the user enters any text then the msgbox triggers, the box is cleared and the focus is set back to it for them to enter only a number. Once a number has been entered then another msgbox should trigger to ask them if they want to enter the mileage for another day 'textboxDay2Miles' if they say yes, then 'textboxDay2Miles' is enabled and the process starts over again. (I have 5 days on the form but only need this routine to apply to 4 days)

Added into this I have a 'Total Mileage' textbox which updates the total mileage dependant on how many days a value has been entered in. this is coded with a sub called 'TextboxesSum'

I can get the text value check routine working if I comment out the ''TextboxesSum' sub but otherwise it errors out - initially on the first line but all the others subsquently.

I've tried everything to get this to work but to be honest I have very basic knowledge of VBA and whilst I try to resolve any problems myself through searching out answers on the net this one is beyond me, so I would be really grateful for any help offered.

Many thanks Paul

Code below:

Private Sub textboxDay1Miles_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    If Not IsNumeric(textboxDay1Miles.Text) Or Trim(textboxDay1Miles.Value) = " " Then
        
        MsgBox "It looks like you entered a text value." & vbNewLine & _
        "Please enter only a numeric value.", vbExclamation, "Text Value Entered"
        
        formMileCalc.textboxDay1Miles.Value = ""
        formMileCalc.textboxDay1Miles.SetFocus

    Else

    TextBoxesSum

End If

End Sub

Private Sub TextBoxesSum()
    Dim Total As Double
    Total = 0

    If Len(textboxDay1Miles.Value) > 0 Then Total = Total + CDbl(textboxDay1Miles.Value)
    If Len(textboxDay2Miles.Value) > 0 Then Total = Total + CDbl(textboxDay2Miles.Value)
    If Len(textboxDay3Miles.Value) > 0 Then Total = Total + CDbl(textboxDay3Miles.Value)
    If Len(textboxDay4Miles.Value) > 0 Then Total = Total + CDbl(textboxDay4Miles.Value)
    If Len(textboxDay5Miles.Value) > 0 Then Total = Total + CDbl(textboxDay5Miles.Value)

   textboxTotalMiles.Value = Total

End Sub

Private Sub textboxDay1Miles_Change()

    TextBoxesSum

End Sub

Private Sub textboxDay1Miles_Change()

    TextBoxesSum

End Sub

 

[RoryA]

You don't want SetFocus in that event - just set Cancel to True:

Private Sub textboxDay1Miles_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    If Not IsNumeric(textboxDay1Miles.Text) Or Trim(textboxDay1Miles.Value) = " " Then
        
        MsgBox "It looks like you entered a text value." & vbNewLine & _
        "Please enter only a numeric value.", vbExclamation, "Text Value Entered"
        Cancel = True
        Me.textboxDay1Miles.Value = ""
       
    Else

    TextBoxesSum

End If

End Sub

You didn't say what error you were getting?

 

[Pacman52]

Hi Rory - once again thank you so much for replying

The error is a Run-time error '13' Type mismatch