Invalid property value in userform when values cleared from combobox

I'm having a slight problem that I'm hoping someone can help me with. I have a userform that I've created, that seems to work fine.........one time! After data is submitted, I have cleared the combobox's values with this command:

Please Login or Register  to view this content.

etc

which clears the values, but has the unintended consequence that the form becomes as sensitive as a 17 year old with their first hangover - if you click anywhere on the form, you get continuous 'invalid property value' dialog boxes popping up - I'm presuming because I set the combobox's property to 'exact match' (which I want), but that "" is NOT an exact match. One of the comboboxs is dependent on the other, and the rowsource looks up about 50 different named ranges, and adding "" to all of them seems like a convoluted solution.

Is there a command for 'reset the combobox' after data submission, without offending the 'exact match' criteria? I've googled around but I can't find a solution. Even some code that prevents ANY keypress data entry would work (I think), because then I could remove the exact match property.

Any thoughts or suggestions gratefully received, thank you.

Set the ListIndex property to -1.

What cytop said.

Or, alternatively you could add an IF statement to the combobox code that changes the rowsource for the other.
Something like

Please Login or Register  to view this content.

BSB

Originally Posted by cytop

Set the ListIndex property to -1.

I'm afraid that didn't work either - although again it did clear the values, I still get the same 'invalid property value' error message.

Then something else is firing when the Listindex is changed and it refers to the ListIndex... probably. it's impossible to say without seeing the code/userform.

Originally Posted by Norie

Any chance you could upload a sample workbook?

Click on GO ADVANCED and use the paperclip icon to open the upload window.

Thank you all for your help, I'm afraid I don't think I can upload a sample workbook because it contains lots of intellectual property information and calculations with vlookups and named ranges etc, and if I stripped it all out the userform would probably be confusing and fairly meaningless.

This is my userform code anyway, I appreciate that nobody has time to look through it all, but if there is something screamingly obvious. I have also realised that I have the same problem with my second userform, but that isn't surprising because it's the same butchered code I used for the first.

Thank you all for trying anyway

Please Login or Register  to view this content.

Do the named ranges that you're applying to ComboBox2 resemble the values that can be selected in ComboBox1?
i.e. If ComboBox1's options were "Fruit", "Veg", "Meat". Do you have named ranges called the same?

BSB

Originally Posted by BadlySpelledBuoy

Do the named ranges that you're applying to ComboBox2 resemble the values that can be selected in ComboBox1?
i.e. If ComboBox1's options were "Fruit", "Veg", "Meat". Do you have named ranges called the same?

BSB

Mmmm, kinda, sorta, ish! (they do resemble, but not the same). I don't know if this was the most efficient way to do it (probably not), but I used the "case" thing, and basically said 'case combobox 1 = this, then the rowsource for combobox 2 = that'. The named ranges are quite similar, the options in combobox one are things like '1a blah blah blah blah', '4b la la la', then the named ranges are called 'a1', and 'b4' etc (coz the name for ranges can't start with a number).

Without sight of the workbook this is all guesswork, but perhaps something like adding the red text below would help?

BSB

Please Login or Register  to view this content.

Originally Posted by BadlySpelledBuoy

Without sight of the workbook this is all guesswork, but perhaps something like adding the red text below would help?

BSB

Please Login or Register  to view this content.

Thank you very much, I'll try that, although I'm not sure it's just the 'case' thing that's causing the problem, I think it might be affecting the other comboboxes too (not sure, the dialog box just seems to spring up when I click in any input box after adding data the first time). I will work this weekend on trying to strip out all information but leaving the functionality the same, I'm sooooo close to completing this project, and this is my first vba foray, just a few things to go! Thank you very much again for all your help, it is very, very much appreciated

Sorry if none of my suggestions help, but it really is difficult without being able to tinker with the workbook.

Hope you get it all sorted soon. Good luck.

BSB

This is my workbook, I've stripped out all the confidential data (please god) which might have affected it slightly - I used Beatles songs instead, then couldn't remember all the ones I'd used! I removed all the vlookups and stuff coz they were redundant without the data. Anyway, I did try it, and the problem does seem to be the same. If anyone could tell me what to do, that would be extremely kind. Thank you everyone for your help.

Try changing the change event code for the first combobox to the code below and see if that does what you need.

BSB

Please Login or Register  to view this content.

Originally Posted by bevc

Add: the more I look at it, the more obvious it is that the comboboxes 'resetting' is the problem - the forms work fine when first opened, it's only after data has been submitted, then cleared, that the comboboxs have 'invalid properties' if you click on them - and if I don't run the 'reset' code for the comboboxes, it all works, except of course that the comboboxes are left with the previously entered values after the other data is cleared. So basically after data has been submitted I need to find a way to reset the comboboxes to how they are when it's first opened.

You can try adding the blue line:

Please Login or Register  to view this content.

I found a workaround, I added 'Select' to the first combobox, and then disabled the second combobox as per BSB's very helpful post. It's not a very elegant solution, but it seems to be working. Thank you very much everybody