Data Validation in User Form -erratic

[AlanAnderson]

Hi All,

I have the following data validation routine. I am getting erratic behaviour within my programme that seems somehow linked to this.
Is there a problem with this or should i be looking elsewhere?

Regards,

Alan

Private sub tbTaxYN_Change()_
If UCase (tbTaxYN) = "Y" or UCase (tbTaxYn) = "N" then
Goto EndProc:
Else
MsgBox "Please enter either a Y or a N"
End if
EndProc:
End sub

 

[charleymax]

Hi All,

I have the following data validation routine. I am getting erratic behaviour within my programme that seems somehow linked to this.
Is there a problem with this or should i be looking elsewhere?

Regards,

Alan

Private sub tbTaxYN_Change()_
If UCase (tbTaxYN) = "Y" or UCase (tbTaxYn) = "N" then
Goto EndProc:
Else
MsgBox "Please enter either a Y or a N"
End if
EndProc:
End sub

I might be wrong here but it looks like you are wanting to force a column data entry to be Y or N...

Wouldn't you be better (& easier) to highlight the entire column and use data > validation..?

 

[VoG]

Hi. This would be simpler

Private Sub tbTaxYN_Change()
If UCase(tbTaxYn) <> "Y" And UCase(tbTaxYn) <> "N" Then
    MsgBox "Please enter either a Y or a N"
End If
End Sub

 

[AlanAnderson]

Hi CharleyMax And Peter,
Charley - no I'm trying to force correct entry into a form.

Peter, thanks, that's a lot more elegant. I'll try it and see if it works.

Thanks,

Alan

 

[AlanAnderson]

Hi Peter, Tried it but still have two issues:
1. It is displaying msgbox as form is displayed even though defaults in the field are correct.
2. If I type in rubbish (say q) it brings up msgbox but after pressing OK I can still go ahead and save the "q"

Any thoughts??

Regardsm

Alan

 

[VoG]

Possibly

Private Sub tbTaxYN_Change()
Static iRun As Boolean
If iRun Then Exit Sub
If UCase(tbTaxYn) <> "Y" And UCase(tbTaxYn) <> "N" Then
    tbTaxYn.Text = ""
    MsgBox "Please enter either a Y or a N"
    iRun = True
Else
    iRun = False
End If
End Sub

 

[AlanAnderson]

Hi Peter,

Thanks again for your help. I must have some other issue in my programme as I am still getting same problems. The logic in your routine is clearly not the problem.

Thanks again,

Alan