VBA Coding upon error message

**knighty2002uk** · 03-21-2013, 07:57 AM

I've created a form that when the user clicks submit, they get an error message if one of the fields are blank. The issue is that the rest of the fields populate. What I want the VBA to be able to do is go back to the form with all previous information still present so the user can fill in whats missing, but the rest of the information isn't populated on the excel spreadsheet.

If this is too hard, or can't be done, I want it to be able to clear the form with an error message but again, nothing populates.

Hope you guys can help me with this one.

**Norie** · 03-21-2013, 08:06 AM

This could be straightforward, it depends on what code you currently have?

Can you post that?

**knighty2002uk** · 03-21-2013, 08:25 AM

If you can solve this I would be very gratful. Also if you see a way of streamlining this coding, again I would be grateful. Until 2 weeks ago I new nothing about VBA/Macros and have self taught. Think I've ceilinged on this one. lol

Please Login or Register  to view this content.

**knighty2002uk** · 03-21-2013, 10:12 AM

Can someone comment, if they are looking into my query, even if not solved yet so I know that it's got attention. TY

BUMP

**Norie** · 03-21-2013, 11:32 AM

Only just had a chance to have a look at the code.

The main problem is the order of the code.

All the code that puts values on the worksheet should be after the code that validates the controls.

The other thing you need to do is exit the sub if there's a problem with one of the controls.

You'll also want to set focus on the control with the invalid data.

So you would want to change an If like this,

Please Login or Register  to view this content.

to this.

Please Login or Register  to view this content.

**knighty2002uk** · 03-21-2013, 11:39 AM

Thats worked 1/2 the problem out where I want the document to remain open with the information still present but it's still populating 1/2 the form when I click submit. Until all boxes are filled, I don't want it to populate anything.

Thanks so far for the help Norie, hope we can close this one soon

**Norie** · 03-21-2013, 11:41 AM

How exactly did you change the code?

**knighty2002uk** · 03-21-2013, 11:42 AM

This has been replicated through all text box command lines. Just posted one to save space.

Please Login or Register  to view this content.

**Norie** · 03-21-2013, 12:05 PM

You still have the code to populate the worksheet before the code that validates the control.

All the If statements should be at the top of the sub and the code to populate the worksheet should come next.

I can't post the code for that right now, not at a computer.

**Norie** · 03-21-2013, 12:50 PM

This is the sort of thing I mean.

Please Login or Register  to view this content.

You mentioned streamlining the code? One way you could do that is to exchange the 12 month checkboxes for one multiselect listbox set to display checks for each month.

**knighty2002uk** · 03-22-2013, 04:03 AM

OMG, I just didn't think about the logical order of the coding. You are so right, that is fantastic. Your assistance and advice has been spot on. Many thanks Norie.