Excel, data capture using VBA form

[Stevo911]

Hi

Im in the process of designing a form in excel which captures certain fields and then inputs the data into the relevant places on a spreadsheet running in the background.

My problem is that when i check for errors, e.g. a blank field ...

*****

Select Case busname
Case ""
MsgBox "The required field 'Business Trade Name' is empty."

End Select

*****

After the user clicks "ok" to get rid of the MsgBox the rest of the code is executed. I've tried putting a "stop" in but this jumps into the code.

My question: is there a way of pausing the process here so that the other correct values entered into form can remain intact allowing the user to correct the necessary field and then click the "submit" button again once they're finished.

Any ideas and suggestions would be greatly appreciated

Thank you
Steve

 

[xlbo]

you need to end the sub, then run it again after they have re-entered the data correctly

Select Case busname
        Case ""
            MsgBox "The required field 'Business Trade Name' is empty. Please complete this field and re-submit"
            Exit Sub
End Select

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[PHV]

Provided that busname is a TextBox in the current UserForm you may call the busname.SetFocus method before exiting the sub as xlbo showed you:
MsgBox "The required field 'Business Trade Name' is empty. Please complete this field and re-submit"
busname.SetFocus
Exit Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Stevo911]

Thanks guys ... that helps!