Re: Validation Of Data On Worksheet_change Event
First and foremost mate. I'd remove the on error resume next statement so that when you do have an error you can isolate where it occurs.
Obviously your problem is if the vlookup returns an error you don't want it to debug. therefore try using the vba find function instead: (I'd suggest wrapping it into your own function for later use as this becomes extremely handy)
Sub PassVLOOKUPtostring() Dim sResult As String If Range("CLI").Find("hello", , xlValues, xlWhole, xlByRows, xlNext) Is Nothing Then sResult = "error performing search for 'hello' in Range 'CLI'" Else sResult = Range("CLI").Find("hello", , xlValues, xlWhole, xlByRows, xlNext).Value End If MsgBox sResult End Sub
Now as for point 1. the userform opens regardless due to your On Error Resume Next statement not controlling the error thrown by your vlookup formula. as mentioned above try the find function the way i have shown and you will get much better results.
With regards to point 2. You haven't declared textbox1 as Userform1.Textbox1 and again due to your error handling it is just running on through.
hope this helps.
My tip is always remove On Error resume Next statements. They cause way too much hassle and i guarantee that you can get by without them provided you learn a little bit more about what you are trying to return from various functions.