For Each..Next Loop Issues

Attached is a file that I am having a little trouble with. The Master BOM Sheet has a button to process 20 different Group BOM Sheets based on the Value of the Group Unit Qty. We have been using this file for some time now and I was recently asked to modify the process.

Currently the Process Group BOM Button is linked to UserForm1 which has a ListBox with Group 1 - Group 20. The user double clicks the Group that they would like to process and the form closes. If the user is processing all 20 groups, it gets a little tedious to press the button 20 times and double click each individual Group. I have created UserForm3 with CheckBoxes and an OK button in hopes that if the user checks 3 boxes that the For Each Loop will process all 3 sheets one after another. Here is where I need some help.

If one box is checked, it processes the Group BOM just as UserForm1 Does, but if multiple boxes are checked, it will not process any of them. I also noticed that if I open the UserForm3 and process one Group, then open the form again and cancel out of the form to close it, the data that was last processed gets erased from the Group BOM Sheet.

Right now the sheet code is set to open UserForm3 in the Sub FillLstBox procedure (I left the terminology the same until I get the process complete) when the button is pressed. To see how the process works with the ListBox doubleclick event, change the Procedure to UserForm1.

I had to remove a lot of sheets and additional VBA from this tool to get it on here. If someone wants to see the complete tool to get a better understanding of what is going on, I can send it in Email just let me know.

Really need some help here.

I am not having any luck getting that to work. When I select an item in the listbox it highlights but the code does not run and no errors are thrown. Multiselect is set to True

I thought of a Process All option, but was not sure how to loop through each Group if the Process All option was selected. The checkbox solution was asked for by the director of sales, so I thought I would attempt his request and was not sure how well it would run if it looped through all 20 groups for every update they make. We seldom have all 20 groups but need to be able to accommodate if we do. I have even modified this tool to go up to 35 groups for one customer.

110 Views so far and no one out there knows how to help with this one. For those who are looking and did not want to download the file, here is the code being used.
Code in Standard Module

Please Login or Register  to view this content.

Code in Worksheet with Button - 1

Please Login or Register  to view this content.

Code in Worksheet with button - 2

Please Login or Register  to view this content.

UserForm3 Ok Button Code

Please Login or Register  to view this content.

Norie,
I know that their is quite a bit of code in this particular file, I was only trying to post the code related to this process. If I missed any in the post above it was not intentional. I will look back through and see what I missed.

Thanks for the help

Xero Solus
I want to open UserForm3 and based on which Checkbox is selected, I want to locate the Corresponding Group across Rows I - AV and when found search down the rows and locate a number greater than 0. When found, I want to store the line number listed in column B into the array and when all rows (300) have been searched and logged, the code will open the Worksheet with the same name as the column and the Checkbox caption and place the logged array into the Drawing Nnumber Column. This feature is fully functional with the listbox in UserForm1, however it requires the user to select and update each sheet on an individual basis.

I am trying to loop through the Checkboxes and find the ones that are checked. If they are checked, I want to copy the line number into the array then open the corresponding sheet and populate the column with the array. So if Group 1, Group 2, and Group 3 are checked in UserForm3, I want to go down the Group 1 Unit Qty Column (I) find all of numbers that are greater then 0 then populate Group 1 BOM (Sheet), then repeat for Group 2 BOM (Sheet) and Group 3 BOM (Sheet).

As mentioned, this process is functional when UserForm1 is set to Open with the button on the Master BOM Worksheet. The problem is, when Group 1 is double clicked in UserForm1, it searches and populates then closes UserForm1. The user then has to press the button again to open the UserForm to process the 2nd Group then the 3rd and potentially up to Group 20

Xero Solus,
I was using the following
Dim FrmCtrl As Ctrl

Please Login or Register  to view this content.

I will try your version and see what happens. Right now the version I have will populate only one Group at a time. If I check more than one box it does not do anything.

ZS,
As I thought, when I change my version to yours it still works the same way. It will find only 1 marked checkbox and process the code for one group sheet at a time. If more then one box is checked, it will not process any.

ZS,
Scratch that I just reread your post. Are you saying to create a sub routing that has the code that completes the process and then Call that sub routine from the OK button on my userform? I will attempt this and see if it works. I am assuming I can just add a new module, call it Sub Process() and then put the code into it. Then call Process from the OK button.

ZS,
Ok so I changed the code as follows. I error out in the Sub Process.

Please Login or Register  to view this content.

thanks for the help.

The loop through the group number is where the code is looking at the Line selected in the userform and finding the same name in row 26.

You were refereeing to this section here correct?

Please Login or Register  to view this content.

The reason it steps 2 places is because of the hidden Ext. Qty Column for each Group.

Yes it does highlight the line of code for FrmCtrl.Caption. I wrote in the code 'error here. I should have mad it all caps so you could see. it.

Ok, Now it will populate the correct sheet if one item is selected as before, but if multiple items are selected, it only populates the sheet related to the last checkbox

Thanks for you help. I have only been VBAing for a little over a year. I will start using more comments going forward I see where it is helpful to declare what is going on.

I like the checkbox option in the ListBox. I did not know that was possible.

XS,
I can take either one. I just wish I knew that I could change that property before I spent a few hours creating UserForm3 with 20 Checkboxes and a 4 Page Multipage Control and a bunch of other buttons. Although I would not have thought of the .selected Item code that you added either. They came in asking for checkboxes and I immediately thought ok For Each Checkbox that is true walahhh. You are the pro, I will take whatever is easier, but am curious as to what will get the Checkboxes to work.

Ok, I like that the Lookups will not be needed, as it will decrease the size of the workbook, because all 20 sheets for the Group BOMs lookup back to the Master BOM based on the MBOM/DWG #.

Group 1 BOM (sheet) should populate with The MBOM DWG #'s 1 and 2 because the Unit Qty Column is greater than 0. (If the part number is not used in the group, we don't need it to show up in the Group BOM sheet.) This sheet populated with correct info for Description, Part #, and Unit Qty for the 2 lines that show up with having a unit qty. The MBOM DWG # and the Order Qty are missing.

If I make an update to Group 1 in the Master BOM to where I need to add another part number, I will then need to update the Group 1 BOM by pressing the button to process again. I found that it duplicates the info that is already in the sheet, by adding to the next available row. So where Group 1 had lines 1 and 2 with a Qty of 1 and 3, I added a Qty of 55 to line 10 and wanted to update the Group 1 BOM. This ended up giving me lines 1 and 2 from the first process, then put 1 and 2 and 10 below the first 2 lines, thus giving me 5 Part numbers for Group 1 2 of which are duplicates. (the old code would delete the MBOM DWG # before it populated, this way it was always populating a fresh set of data starting at the first Row below the header, and it never had a duplicate item).

Group 2 BOM should populate with MBOM DWG # 1 and 3 but for some reason it populated with 1 and 10. In the Master BOM, Group 2 Unit Qty for MBOM DWG # 10 has a Zero Qty it is only used in Group 3 so should not show up in the Group 2 BOM Sheet. Once again when I do an update on the individual Group, the items duplicate into the next available rows and the MBOM DWG # and Order Qty are missing.

Group 3 Should populate with MBOM DWG # 10, which it did with the Description and Part number respectively, however the Qty should have been populated within the Group 3 BOM with 10 as it is the Master BOM but it seems to be populating with the Group 1 Unit Qty.

The good news, is that the Multiple checked items do populate simultaneously which is what I was trying to do.

So to sum it up.
1. Clear cell contents of each Group BOM before it populates to prevent duplicate entries. When updates need to be done. Updates include changing quantities, and or part numbers.
2. Populate the Group BOM with MBOM DWG #, Description, Manufacture, Part #, Unit Qty, and Order Qty. (Note: Order Qty comes from hidden Ext Qty Columns next to each Group Unit Qty column.)
3. I like that the Lookups were removed to help reduce the file size. The original file would only populate the MBOM DWG # which provided the info for the rest of the columns.

I will look through it a little more thoroughly to see how it works with some additional Groups being filled in. This file could get to where all 20 groups are in use and need to be populated with as much as 300 different part numbers in one or more groups.

Thanks for the help

You covered everything I found on the previous one thanks it is a lot smoother then the previous code, but now there is a problem when you update one at a time.

If I process all 3 groups (only the first 3 groups populate right now). It works great, but if I need to update one group, say group 2 because I added another part number to it, it updates that group with the new part number but because the other two Groups were not checked as needing to be updated, the data in the Group Sheets gets deleted. This makes it to where you have to process all groups at one time.

So right now the code seems to clear the cells in each sheet then it repopulates based on the value of the check box. What we need is to check the value of the checkbox, If it is false, leave it alone, if it is true clear the cells for that Group BOM only then populate the sheet.

I transferred the code from your last update into a fresh tool, (it looks like I was testing with an outdated copy of the tool) and am successful in updating every group. The down side is, when there are a lot of part numbers in a group, it takes considerably longer to process then before, Even if it is only populating 1 group. I know that the process will take a long time when multiple groups are updated at one time.

Thanks for the help on this. I think if we get the clearing of a worksheet if the Group Checkbox is checked nailed down so that it does not clear all of the Worksheets then this will be done. I can use my choice of a multipage control in the userform as a way to make sure they really only try to populate 5 groups max at a time and if Group 12 gets updated in the Master BOM the user will only have to go to that page in the form and check Group 12 to update.

Ohh looks like you posted an update while I was typing my last update. I will check that one out.

The latest version throws an error in the code below.

Please Login or Register  to view this content.

I fixed the error, it should have said sheets, just missed the S.

It seems to be working fine. Even with individual groups selected. I have tested thoroughly, on this one so far and will try and do some additional checks to see If I can break it before I put it into a live environment.

I was able to populate and update one group and multiple groups. The tool works so far. If I have any issues I will come on back. I don't suppose you could get me some code that is commented to identify what it is doing. I need to be able to go in and update it if I need more than 20 groups for a customer. As mentioned previously, I have had to modify the old version on 2 occasions to include 30 groups and even 35 groups.

I will be marking this as solved because you did fix the tools For Each ... Next Issue. Even though we don't know what the issue really was.

Thanks for the comments, I was able to take the following part of the first loop and put it just below the checking for the the true checkboxes of the second loop and it seems to be working just fine. I also was able to see where to adjust the columns for additional groups within the master BOM. Thanks again for the help, I feel you went above and beyond because you rewrote the entire code, and taught me some more about loops and how they can be structured.

Please Login or Register  to view this content.