Hello,
The attached VBA project is used to enter customer orders for flats of annual flowers. In the "Flower Order Entry" tab, the user selects varieties of flowers from drop-down menus in Column T under "Common Name." The colors available for each variety are then highlighted green in columns U thru AR based on the saved colors for each variety in the "Data" tab. The user then enters how many flats of each color are to be ordered. Finally, the code compiles a summary of the order in columns L thru O.
My code is providing the intended result but after about 5 varieties are entered, the code runs very slowly. There are several nested loops that are used to compile the order summary that, I imagine, could be optimized but I am not sure how. I am looking for suggestions and guidance on how to optimize this code to run more efficiently. I imagine that I am recalculating variables a lot more often than I need to but I am not familiar enough with using public variables to use them confidently. I have been using VBA off and on for a few years now but and I have other versions of this project but they are not as dynamic as I need them to be. My relevant code is below:
Public Functions lrFind and cntUnique are used to evaluate the saved varieties and colors in the "Data" tab to define and resize various ranges and compile a list of unique colors available in the "Flower Order Entry" tab:
Worksheet_Change Procedure every time the user enters or changes a value in the Order Entry table:
Module 1 Sub to Update the Order Summary in Columns L thru O:
Thank you all, in advance, for any help.
Bookmarks