Clearing a Combo Box then trying to submit user form to Workbook gives error messages

**colvinb** · 05-19-2012, 01:34 AM

I have created a user form that collects information about a job then once the ‘Submit Record’ button is pressed adds the information to a table within the workbook.
Within the user form are rows of combo boxes that are filled in from left to right and the selections made in the first 2 combo boxes then tells the user form how to populate the 3rd combo box. (The 3rd combo box is populated from different ranges within another sheet of the workbook and has 3 columns)
Pic of user form row of combo boxes Below

Screen Shot 2012-05-19 at 12.58.09.png

To the right of each row is a ‘reset button’ which clears all combo boxes in the row. This allows the user to re-enter data but might be causing problems when I try to submit the form with the combo boxes blank after the reset button has been pressed.
Code below;

Please Login or Register  to view this content.

My problem comes when I try to ‘Submit Record’. I was using the below code to add the values from the combo boxes to my table;

Please Login or Register  to view this content.

Which works fine if the combo boxes are populated or untouched by the user but if the combo boxes were selected then the ‘reset’ button was pressed to clear the boxes then the user form was submitted I get the run time error 381 “Could not get the Column property. Invalid property array index”

(this is my first post so if I have not explained something properly or more code/ pics are needed then please just ask)
Hope someone can help…
Thank-you in anticipation

Ben

**Mordred** · 05-19-2012, 03:22 AM

You would probably gain a solution quicker if you uploaded the workbook without sensitive and private data. It's hard to say without knowing what is happening in the CalculateMachineAndActivityProductionTotals procedure regarding why you are getting that error. Perhaps having a 0 value in MachineCB01 contributes to throwing you that error. Yep, your best bet is to upload a workbook. Unless of course someone else here is seeing something I am not.

**colvinb** · 05-19-2012, 03:52 AM

Hey, thanks for the reply, unfortunately my workbook is 3.4MB so too large to upload (unless there is another way of doing so that Im not aware of)
Would you like me to email the workbook or post more of my code?
Thanks
Ben

**Mordred** · 05-19-2012, 03:46 PM

Hi colvinb, have you tried zipping it? Probably won't be enough but I thought I would ask.

**colvinb** · 05-19-2012, 08:20 PM

ProblemWorkbook.zip

Hi Mordred

I've deleted a lot of un-related sheets then zipped it, can't believe i didn't think of that!

Please try not to laugh too hard at my code as I'm pretty new to VBA and theres probably MUCH shorter ways around what i've done!

Thanks again

**Mordred** · 05-20-2012, 11:36 AM

Hi colvinb, Your code is set up nicely with indents which makes it easier to read so no worries about me laughing too hard. However, there are a few issues to discuss.

First, code like:

Please Login or Register  to view this content.

cannot work and more specifically

Please Login or Register  to view this content.

The IDE doesn't know what NA is so that alone will throw an error. You want NA to be a string value I assume so you have to cast it as a string, like

Please Login or Register  to view this content.

Second, this line

Please Login or Register  to view this content.

can be problematic for the IDE to know what sheets you want your cells' values to go onto. I took a couple lines with a declared worksheet to show you what I mean

Please Login or Register  to view this content.

Doing like I just did helps you to keep a focus on which sheets need what as well as lets the IDE know where to place the values.

So, what I would suggest to you is to go through your code and look for values that you want as strings and put quotes around them.
Set Variables like I did with wsMachine

Please Login or Register  to view this content.

In the workbook I am sending back to you, you will notice that I used

Please Login or Register  to view this content.

at the top of the userform module. Using this forces you to declare variables at compile time. You will benefit greatly from declaring variables like Worksheet, Range, Double, Long, and et cetera. As it is now, you can click Debug in the IDE and then click Compile VBA Project with option explicit on and the compiler will take you to every variable issue in your workbook. I would have done that for you but there is a lot of code in your workbook so I'll leave that up to you. I did add quotes to all of your "NA"'s though. Straighten up your variables and if you need more help with new issues you know where to find the answers.

Good Luck!

**colvinb** · 05-21-2012, 04:42 AM

Dear Mordred
Thank-you so much for your time and patience with me.
I have gone through and cleaned up my code, defining all my variables etc and now the original if_then_else code works perfectly with your changes to quotes around the NA.
I got stuck on defining emptyRow as a variable but as the issue wasn't related to my initial problem title I posted a new thread and have also been guided through to resolution.
Thank-you so much again, as soon as I have gained the smallest amount of knowledge and experience in this field I hope to help other beginners too
All the best
Ben

**Mordred** · 05-21-2012, 02:18 PM

More than glad to help.

Clearing a Combo Box then trying to submit user form to Workbook gives error messages

LinkBack

Thread Tools

Rate This Thread

Display

Clearing a Combo Box then trying to submit user form to Workbook gives error messages

Re: Clearing a Combo Box then trying to submit user form to Workbook gives error messages

Re: Clearing a Combo Box then trying to submit user form to Workbook gives error messages

Re: Clearing a Combo Box then trying to submit user form to Workbook gives error messages

Re: Clearing a Combo Box then trying to submit user form to Workbook gives error messages

Re: Clearing a Combo Box then trying to submit user form to Workbook gives error messages

Re: Clearing a Combo Box then trying to submit user form to Workbook gives error messages

Re: Clearing a Combo Box then trying to submit user form to Workbook gives error messages

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions