Multi-Level Array Type Mismatch Error

**CrazyFileMaker** · 06-03-2009, 11:51 AM

Hello,

When using MsgBox to display the value of a multi-level array, I'm getting run time error 13 for a type mismatch.

I've included the excel file.

The problem only occurs when I select a value in the ComboBox labeled "Wd". To select this value, simply click on the toggle button labeled "Wd" and then use the ComboBox and you'll get the error.

Could you take a look at this for me? I've been debugging for two days now and I've tried changing many things, like, all strings to variants and reassigning different values to the ComboBoxes... still i'm getting the same error.

If you need to know more about the code, the following is a brief explanation. If not, skip it. But maybe it will answer potential questions.

Thank you so much for the help!!!!

The array I've created is 5 levels to begin with. Through a sub-procedure the contents are moved from the 5 level array to a 4 level, 3 level, 2 level, or 1 level array, based on the needs of the user. The user chooses which array he will use (clicking a toggle switch and changing the value of the variable named "Hierarchy".) He then selects an index in that array (by way of a combobox which assigns the value of the combobox to a variable named Ch_1_Box_UserChosen, for example) to obtain its value.

All levels hold a value of Long except for the bottom level which holds an actual string. However, due to debugging the problem I've described, the string variables are now all declared as variants in my attempt to fix the problem. This created no new problem but fixed nothing as well.

The problem does not occur when the user selects the 5 level array, the 4 level array, 3 level array, 2 level array, and one of their values. Those values are returned without an error. But when the user selects the single level array, while the value IS displayed correctly, immediately after its shown I get a run time error 13 mismatch.

The point of all the levels is that they represent a book. The top array is the chapter, then section, then paragraph, then sentence, then word. The user can select a single word by searching for it throught the whole hierarchy of levels or just by going to certain levels.

For example, The first word in the book can be referenced either by saying: chapter 1 Section 1 Paragraph 1 Sentence 1 Word 1, or by simply saying word 1, since the first word in the book also happens to be the same as the first word in the first chapter in the first paragraph etc. etc.

This probably sounds confusing but i think the code will make it clear.

Thanks for taking the time to look at this! It should probably be an easy fix but I'm new to VBA and I can't find the solution for the life of me.

Thanks again so very much!!!!!!

**rylo** · 06-03-2009, 11:20 PM

Hi

I think you are getting into a loop with the events. cboWord has an event that is fired when you make a selection. This goes through and presents 2 messageboxes. However, when this has been completed, cboWord is set to "" in, I think, SkipResults, which then fires off the event again. This brings you eventually to the messagebox where the text includes

TheIndex(Wd_Box_5_UserChosen)

However, as cboWord.value = "", then Wd_Box_5_UserChosen is "" so TheIndex() will error.

Try, but I don't know exactly where at this stage, doing an

Please Login or Register  to view this content.

which will stop the refiring of the cblWord event and going again through the loop.

Make sure that you have an error trap on Main that if there is an error of any sort, one of the actions is

Please Login or Register  to view this content.

HTH

rylo

**shg** · 06-04-2009, 12:12 AM

I readily confess to not reading this thread in detail, but disabling events does not affect events firing in forms.

**CrazyFileMaker** · 06-04-2009, 04:33 AM

Thank you so much, Rylo! You've put me on the exact problem for sure! UserForm_Activate is definitely looping twice.

I'm so close to a solution now!

All I have to do is figure out why UserForm_Activate loops twice when a value is selected in the ComboBox named "cboWord".

UserForm_Activate does NOT loop twice when a value is selected in any other ComboBox, and does not give an error. Only for "cboWord".

I've attached a cleaner version of the code. It isolates the problem. This way it's much clearer to see. Also, I marked where the problem is located.

This is what I've come up with so far:

The problem is located in the procedure "togWord". Where "togWord" is set to false, the statements cboWord.Value = "" and cboWord.Clear seem to be causing UserForm_Activate to loop a second time for some reason.

If I comment out both cboWord.Value = "" and cboWord.Clear, then UserForm_Activate does not loop twice.

Can someone tell me why that is?

I can't just eliminate the two statements to fix the problem because i have to reset the Value in the ComboBox and I have to clear the List in the ComboBox as well.

So why are these two statements causing UserForm_Activate to be called a second time? Does vba work in such a way that these two methods call UserForm_Activate whenever they are used? And, if so, why is this not occurring when the other toggle switches are set to false and employ these methods?

So. Can anyone tell me why UserForm_Activate is looping twice whenever a value is selected using only the ComboBox labeled "cboWord"?

You guys are a life saver! Thank you so much!!

**rylo** · 06-04-2009, 06:20 PM

Hi

SHG - you are right. I didn't know that you couldn't stop form events that way.

crazyfilemaker - it happens because you change the value in the combobox, and this fires off the event. Maybe you could add something to the event code that it only does the message production if the value is not "".

rylo