Excel Userform textbox data validation

Question

I'm creating an Excel Userform. I want to validate two textbox values.

if combobox1.text= 1 to 2yr then textbox2.value =>1 and <=2
combobox11.text= 3 to 10yr then textbox2.value =>3 and <=10
combobox1.text= 11 to 15yr then textbox2.value =>11 and <=15
combobox1.text= 16 to 20yr then textbox2.value =>16 and <=20
end if

hi, your syntax looks wrong ... but to help fix, it would be good to clarify what you're trying to achieve. Are you looking to set textbox2.value based on textbox1.text? ... or are you trying to validate both textboxes and do something else? And is a single number typed into textbox1? thanks — richaux
– richaux, Commented Feb 16, 2019 at 14:18
if age group text is 1 to 2yr then age must inbetween 1 to 2 if greater or lesser value then show me msg " plz enter correct value " — Yogesh
– Yogesh, Commented Feb 20, 2019 at 13:55

richaux · Accepted Answer · 2019-02-20 22:39:36Z

1

The general syntax needs to be something like the following:

If combobox1.Text = "1 - 2 yr" And (CDbl(textbox2.Value) < 1 Or CDbl(textbox2.Value) > 2) Then
    MsgBox ("Enter correct value")
ElseIf combobox1.Text = <another string> And <another range check> Then...
End If

Notes:

You were missing quotes around the combobox1.Text value: you need this to compare to text (String).
The textbox2.Value needs to be converted to a number (Double) to check the range.
You'll need to consider adding validation on your inputs, in case the field is left blank, or other non-numeric values are entered.
I haven't completed the full set of values for combobox1: you can use ElseIf to chain them together.
Once you have the basic structure, you could refactor by writing a separate Function, named e.g. CheckRange, passing the value to check and the acceptable range, rather than repeating your code.
As a way of refining, if you get this far, you could parse the acceptable range from the checkbox1.Text value. I wouldn't worry about this initially though. hth

answered Feb 20, 2019 at 22:39

richaux

2,6722 gold badges37 silver badges42 bronze badges

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

6 Comments

Yogesh Over a year ago

thanks sir , it works great for all criteria except one , If combobox1.Text = "6 month - 1 yr" And (CDbl(textbox2.Value) < 0.6 Or CDbl(textbox2.Value) > 1) Then MsgBox ("Enter correct value"), it show me error msg. even i enter correct value eg = 0.7 , 0.8 , 0.9 ,0.10

richaux Over a year ago

Hmm, I'm not sure - your code in the comment looks ok. Maybe if you edit your question and show your full code, that may highlight something? Also, do you mean an error message, or the MsgBox? If an actual error message, then you may need to check your input, e.g. no trailing spaces, as these may prevent the CDbl from working. Lastly, 0.10 wouldn't be in range: you're inputting a decimal, not the number of months.

Yogesh Over a year ago

yes sir , one spelling mistake done in code , thats why its show me msgbox. now code run perfectly ,

Yogesh Over a year ago

lastly u said "0.10 wouldn't be in range: you're inputting a decimal, not the number of months." so how can i use 0.10 , 0.11 values in above code

richaux Over a year ago

you can't directly use 0.10 to represent 10 months ... as a number it is one tenth. For example, ten months as a decimal would be 10/12 = 0.83 ... and 0.10 as a fraction of a year is 1.2 months! You could use two textboxes, one for years and one for months, and apply a calculation.

|

Collectives™ on Stack Overflow

Excel Userform textbox data validation

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related