Validate textbox on userform

[bisel]

Hello All,

I have been searching and have found several topics on validating textboxes on userforms, but have not been able to find an answer and hoping someone can assist.

I have a userform with several textboxes. In my instance, I have a textbox that the user enters a year. I need to be sure that the year entered is equal to or greater than a year in a specific cell on the worksheet. If the year is less than that year, I want to capture that occurrence, display an error msg, delete the user's entry and set focus back on that text box.

I have tried this ...

Private Sub dues2_yearstextbox_AfterUpdate()

If dues2_yearstextbox.Value < Range("incr2_earlystart").Value Then
     MsgBox "Please enter a value less than or equal to the earliest start year."
        dues2_yearstextbox.Value = ""
        dues2_yearstextbox.SetFocus
        Else: End If
        
Application.Run ("update_controlpanel")

End Sub

But that does not work. I use the AfterUpdate event because I want to run the application "update_controlpanel" after the user updates the textbox ... that macro updates several userform labels.

The cell range, "incr2_earlystart", contain the 4-digit numeric field for the year I am trying validate against.

I did try the textbox event "Change" ... but that did not work either.

Appreciate any assistance.

Thanks,

Steve

 

[jproffer]

How does it not work? Does it not do anything? It looks like it will run your update_controlpanel routine regardless of the textbox value, but other than that...what does it do that you don't want it to...or what does it not do that you're expecting?

 

[bisel]

How does it not work? Does it not do anything? It looks like it will run your update_controlpanel routine regardless of the textbox value, but other than that...what does it do that you don't want it to...or what does it not do that you're expecting?

Hi, thanks for your reply. What happens is just ... nothing. When the user enters a value in the textbox for the year that is less than the reference cell value, the entered value is accepted and it updates the control source cell. In this case, since the year is prior to the earliest start year, the worksheet records it as an error ... but there is no indication using the userform of any error condition. Basically, regardless of the year entered in the text box, the code for textbox executes as if the inserted If statement that tries to validate the enter does not exist.

Steve

 

[jproffer]

You're sure all the names are correct? The textbox name doesn't have any capital letters in it? Same with (what I assume is a named range) incr2_earlystart?

If they are, it should be throwing up the message (as you know). But after the If statement closes, it will still run your label updater.

By the way...as it is now, is it running that?

 

[bisel]

Just tried something that seems to work.

I declared a variable as an integer, then set that variable equal to the textbox variable. Since this an AfterUpdate event, it is the value that the user has entered. My thinking, which is now working, was that a textbox variable is ... text. And when using an IF statement to compare to number, it will result in FALSE. This is the code now ...

Private Sub dues2_yearstextbox_AfterUpdate()
Dim x As Integer

x = dues2_yearstextbox.Value

If x < Range("incr2_earlystart").Value Then
     MsgBox "Please enter a value less than or equal to the earliest start year."
        dues2_yearstextbox.Value = Range("incr2_earlystart").Value ' Set textbox equal to the early start year.
        dues2_yearstextbox.SetFocus
    Else: End If
        
Application.Run ("update_controlpanel")

End Sub

The only part that is not working is the SetFocus statement. But I believe that is because this an AfterUpdate event and once the user enters text, it cannot set focus on the textbox that the user just left. Don't know if that is correct or not, but now working on that issue.

Steve

 

[Fluff]

Try using an exit event in stead like

Private Sub dues2_yearstextbox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim x As Integer

x = dues2_yearstextbox.Value

If x < Range("incr2_earlystart").Value Then
     MsgBox "Please enter a value less than or equal to the earliest start year."
        dues2_yearstextbox.Value = Range("incr2_earlystart").Value ' Set textbox equal to the early start year.
        Cancel = True
        Exit Sub
    Else: End If
        
Application.Run ("update_controlpanel")

End Sub

 

[bisel]

Hello Fluff,

That also works, and the IF statement is working fine. I am just having difficulty getting the focus to reset back on that textbox ....

Private Sub dues2_yearstextbox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim x As Integer

x = dues2_yearstextbox.Value

If x < Range("incr2_earlystart").Value Then
     MsgBox "Please enter a value less than or equal to the earliest start year."
        dues2_yearstextbox.Value = "" ' Set textbox equal to the early start year.
        Range("hoa_dues_start2").Value = ""
        dues2_yearstextbox.SetFocus
        Cancel = True ' cancel the event.
        Exit Sub
    Else: End If

Application.Run ("update_controlpanel")

End Sub

I can live with that if need be, but would prefer to have focus reset.

Thanks,

Steve

 

[Fluff]

Setting Cancel=True should leave you in the txtbox. Try removing this line & see if that helps

dues2_yearstextbox.SetFocus

 

[bisel]

Setting Cancel=True should leave you in the txtbox. Try removing this line & see if that helps

dues2_yearstextbox.SetFocus

Hello Fluff,

I tried that, but there is still no focus on that textbox. I tried Cancel = False also. Same result.

Thanks,

Steve

 

[jproffer]

The Setfocus line is within the If Statement. If that skips the messagebox, it also skips the setfocus and the cancel. IOW, if they put in a good year, then it just runs your label updater and quits.

Nevermind, I'm a dumba$$ ....I see what you're saying now. Have you tried moving the setfocus around within the If statement? Like maybe above the msgbox. I know it shouldn't matter....but this is VBA