Dependable lists with large data - Array required keeps crashing
- Thread starter cacos
- Start date
NARAYANK991
Excel Ninja
Hi ,
Can you check this ?
http://speedy.sh/uYY6Z/cacos-Example.xlsm
Narayan
Can you check this ?
http://speedy.sh/uYY6Z/cacos-Example.xlsm
Narayan
kuldeepjainesl
Member
Hello Narayan and Cacos,
Soory for jumping in your posts.
Narayan, I was watching this solution and it work well but if you add any value the any list in combo box, it only get updated after your reopen the file. How to avoid this so that adding value do not call closing of the workbook.
Another quick question : What the easiest method to expand this to say level 9 in place of 6.
Regards,
Soory for jumping in your posts.
Narayan, I was watching this solution and it work well but if you add any value the any list in combo box, it only get updated after your reopen the file. How to avoid this so that adding value do not call closing of the workbook.
Another quick question : What the easiest method to expand this to say level 9 in place of 6.
Regards,
NARAYANK991
Excel Ninja
Hi Kuldeep ,
The reason for this is that the populating of the first combo-box is done by calling the procedure named Populate_Combobox1 from the Workbook_Open procedure.
If you add a value to the first combo-box , you can manually run the Workbook_Open procedure ; which will do the needful , as far as the first combo-box is concerned.
As far as the other combo-boxes are concerned , they are dynamically populated whenever you make your choice in the previous combo-box i.e. combo-box 6 is populated when you make your choice in the other 5 combo-boxes ; combo-box 3 is populated when you make your choice in the first 2 combo-boxes , and so on.
If you go through the code , you will find that expanding this to more combo-boxes is a matter of :
1. Adding the necessary Populate_Combobox? procedure.
2. Extending the Reset_Comboboxes procedure to include the additional combo-boxes
3. Adding the necessary Combobox_Change procedures in the Sheet section.
I think it should be possible and quite easy too.
Narayan
The reason for this is that the populating of the first combo-box is done by calling the procedure named Populate_Combobox1 from the Workbook_Open procedure.
If you add a value to the first combo-box , you can manually run the Workbook_Open procedure ; which will do the needful , as far as the first combo-box is concerned.
As far as the other combo-boxes are concerned , they are dynamically populated whenever you make your choice in the previous combo-box i.e. combo-box 6 is populated when you make your choice in the other 5 combo-boxes ; combo-box 3 is populated when you make your choice in the first 2 combo-boxes , and so on.
If you go through the code , you will find that expanding this to more combo-boxes is a matter of :
1. Adding the necessary Populate_Combobox? procedure.
2. Extending the Reset_Comboboxes procedure to include the additional combo-boxes
3. Adding the necessary Combobox_Change procedures in the Sheet section.
I think it should be possible and quite easy too.
Narayan
NARAYANK991
Excel Ninja
Hi ,
I am not sure , but the only parameter that can have an effect on the combobox size is the AUTOSIZE parameter ; is this set to TRUE or FALSE ?
Does it happen in the example file I uploaded , or is it happening in your file ?
Have you tried by inserting a separate combobox and seeing whether any repeated selection and change makes the combobox shrink ? What I mean is , let us first decide whether it is the code that is responsible , or is it some setting of the combobox properties.
Narayan
I am not sure , but the only parameter that can have an effect on the combobox size is the AUTOSIZE parameter ; is this set to TRUE or FALSE ?
Does it happen in the example file I uploaded , or is it happening in your file ?
Have you tried by inserting a separate combobox and seeing whether any repeated selection and change makes the combobox shrink ? What I mean is , let us first decide whether it is the code that is responsible , or is it some setting of the combobox properties.
Narayan
Autosize is False. It happens only sometimes, other times it works perfectly. I can't recreate it. I just opened it and it works fine. It's weird.
I did the following, let me know if I did anything wrong: I copied both sheets from your example into a new file, then copied the code from "ThisWorkbook" and then inserted Module1 into "ThisWorkbook".
Everything else remained pretty much the same. I simply inserted a few rows on top to move it down the sheet.
I did the following, let me know if I did anything wrong: I copied both sheets from your example into a new file, then copied the code from "ThisWorkbook" and then inserted Module1 into "ThisWorkbook".
Everything else remained pretty much the same. I simply inserted a few rows on top to move it down the sheet.
NARAYANK991
Excel Ninja
Hi ,
I am equally foxed ; however , your post regarding Module1 has confused me ; Module1 is not inserted into ThisWorkBook ; from the VBE , click on Insert , click on Module ; a new module will be inserted.
Copy the entire code which is present in Module1 into this newly inserted module.
Please verify that all the named ranges are present in your new workbook.
Another point is that if you have inserted rows at the top of the sheet , and the comboboxes have moved down the sheet , have the named ranges Company , Department , Country , City , Employee_Level and Division had their references changed accordingly ? Excel would have taken care of this , but please check.
Narayan
I am equally foxed ; however , your post regarding Module1 has confused me ; Module1 is not inserted into ThisWorkBook ; from the VBE , click on Insert , click on Module ; a new module will be inserted.
Copy the entire code which is present in Module1 into this newly inserted module.
Please verify that all the named ranges are present in your new workbook.
Another point is that if you have inserted rows at the top of the sheet , and the comboboxes have moved down the sheet , have the named ranges Company , Department , Country , City , Employee_Level and Division had their references changed accordingly ? Excel would have taken care of this , but please check.
Narayan
OK, yes all named ranges stayed the same. It all references perfectly and the code works. And it's working ok now, they are not getting smaller for the moment.
I have a new question, maybe you have a better solution that what I'm thinking: now that I have all filters working, I have a separate sheet where the data is.
There are 6 columns that match the criteria on the comboboxes, and it has many responses with different scores each. Next is averaging those scores that match the 6 criterias. I was planning on doing it with averageifs, but it's getting rather complex since it will be a huge formula (considering when you can select the option "All").
Let me know if based on your expertise you can think of a better alternative, something simpler that will consider these "ALL" and match 6 criteria at the same time.
I have a new question, maybe you have a better solution that what I'm thinking: now that I have all filters working, I have a separate sheet where the data is.
There are 6 columns that match the criteria on the comboboxes, and it has many responses with different scores each. Next is averaging those scores that match the 6 criterias. I was planning on doing it with averageifs, but it's getting rather complex since it will be a huge formula (considering when you can select the option "All").
Let me know if based on your expertise you can think of a better alternative, something simpler that will consider these "ALL" and match 6 criteria at the same time.