Announcement

Collapse
No announcement yet.

Dynamic labels on a userform with multiple conditons

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Allround_IT_er
    started a topic Dynamic labels on a userform with multiple conditons

    Dynamic labels on a userform with multiple conditons

    Hi everyone,

    I'm trying to make an order form in Excel with a userform, multipages, frames, labels en textboxes.
    My workbook contains different worksheets: Customers, Products, Fixed orders, etc

    There is one dropdownbox for selecting the right customer. Once selected, you see more detail about the customer (all data are fictional).

    I'm am searching for a way to do following:

    There is a sheet called "Vaste bestelling" (Fixed orders).
    On that sheet, there is a column with the different customers and all the products.
    If there is a "y" then this product is ordered regularly. If there is a "n", this product is not relevant for that customer.

    You select a customer (detail customer is shown) and now the products labels in the frame should show only the products that are regularly ordered by that customer.
    I'm trying but I'm not getting there.
    Who can help me?

    Thx in advance

    PS: sorry for my bad English
    Attached Files

  • XenoCode
    replied
    Who doesn't? But I'd prefer to keep everything on the forum. They like that so others can follow the thread.

    Leave a comment:


  • Allround_IT_er
    replied
    Do you have a private e-mail?

    Leave a comment:


  • XenoCode
    replied
    I'm gone this far so I'll finish off loading data tomorrow - it would be good to re-do how the Vaste bestelling list works as well (especially as it is crucial to the way the products are loaded).

    Leave a comment:


  • Allround_IT_er
    replied
    ok

    Leave a comment:


  • XenoCode
    replied
    "if I add a product on a department sheet, I will have to add the same product on the worksheet "Vaste bestelling"
    A consequence of the way is was designed to start with. A more logical way would be to have a 2 column list (or 3 columns considering my comment about the qualification). Which ever way it is done there will be an elements of manual editing.

    You can use userforms to update the list for a client where you list all products (in a listbox as I had originally) with checkboxes to indicate if this is a preferred product for this client - difficult to explain but let me knock up an example...

    Leave a comment:


  • XenoCode
    replied
    "It should contain all products of all departments" - No problem, you just need to define them on the Vaste bestelling sheet exactly the same as now: Same if you add new clients, just add to the first unused column in row 1 but be aware that the way products are defined now you can only have 1 'Cake' in the Vaste bestelling list - There's no 'qualification' as to what type of Cake.

    I know the code is slightly complicated but it really simple once you understand how it works. You can add new Primary products and as many secondary products as you want, or clients. As long as the Products sheests is updated the code will find it.

    I haven't looked at how the clients are defined but the same strategy can be used (in case it does not already account for new clients).

    The expression is very correct, if maybe a little old fashioned and quaint. I won't bother with the modern equivalent

    Leave a comment:


  • Allround_IT_er
    replied
    I have tried this and it works like a charm! (I hope the expression is correct) :-)
    I also understand your explication now. It's a wonderful idea!

    I have another question.
    The worksheet "Vaste bestelling" now only contains the product from the department "Brood".
    It should contain all products of all departments ("Brood, Boterkoeken and Patisserie).
    I have now three departments (multipages) and I have for each department a worksheet.
    On the worksheet "Vaste bestelling" all the product should come together.
    I could easely copy them from the other worksheets but what if I add a product on a department sheet, I will have to add the same product on the worksheet "Vaste bestelling" and determine "y" or "n".
    Is there another way?

    Leave a comment:


  • XenoCode
    replied
    I've added a new sheet called 'Products'. This has 2 lists:
    The Primary product lines (Bread etc)
    The Secondary products for each major product (Stone baked bread, square bread etc)

    Each had a dynamic named range defined to cover the range..

    When you select a new customer, all the pages are deleted from the Multipage. It then adds back Pages for each Primary product line.. For each added Product line it then gets all the secondary products for that product and adds frames for each to the product page.

    It should be clear what it does if you look at the Products sheet and then run the userform selecting different clients.

    I haven't gone as far as loading the actual products here, just to show dynamic adding pages and frames...

    New product Pages and frames will appear without any code changes - see the 'new' product Chocolate with 2 secondary products. You can add/delete to the lists and the pages/frames will be generated correctly.
    Am rambling on a little, have a look but keep in mind I haven't included loading the actual products to the frames in this example.

    Guess I should add you wouldn't normally delete everything each time a new client is selected so the Pages/Frames need only be generated when the userform loads and just the data changes when the client is changed.
    Attached Files

    Leave a comment:


  • Allround_IT_er
    replied
    I don't quit understand:
    The whole thing can be made autonomous by adding another sheet with the major/minor product descriptions.That was the reason I added another sheet (even though it only included Breads). The full list would look like:
    Brood Op steen gebakken
    Brood Carré brood
    Brood Speciallekes
    Brood Zachte luxe
    Brood Suggestie
    Boterkoeken Boterkoeken
    Patisserie Drooggebak klein
    Patisserie Drooggebak groot
    Patisserie Cake
    Patisserie Mousse/Slagroom
    Patisserie Individueel/punt
    Patisserie Dessert
    Patisserie Boterkreem
    That can then be used to to Add tabs to the multipage control for each unique type of product and then frames to each tab for each sub-product, then the code I have already added can be modified to load frame with the correct products.If you add a new product, or even a new product line, then the code will include that as long as you add the new data to the list.You can also remove a line...
    I understand your point regarding multiple products defined by 1 item. It's true, not a good idea.....

    I would appreciate some code to get me started.

    And so many thanks for the code already :-)

    Leave a comment:


  • XenoCode
    replied
    Sorry about that - I didn't notice your post with the description, I just scrolled to the end and only saw the 'forgot' message...

    The whole thing can be made autonomous by adding another sheet with the major/minor product descriptions.That was the reason I added another sheet (even though it only included Breads). The full list would look like:
    Brood Op steen gebakken
    Brood Carré brood
    Brood Speciallekes
    Brood Zachte luxe
    Brood Suggestie
    Boterkoeken Boterkoeken
    Patisserie Drooggebak klein
    Patisserie Drooggebak groot
    Patisserie Cake
    Patisserie Mousse/Slagroom
    Patisserie Individueel/punt
    Patisserie Dessert
    Patisserie Boterkreem
    That can then be used to to Add tabs to the multipage control for each unique type of product and then frames to each tab for each sub-product, then the code I have already added can be modified to load frame with the correct products.If you add a new product, or even a new product line, then the code will include that as long as you add the new data to the list.You can also remove a line...

    The idea of multiple products defined by 1 item is usually accepted as not being a good idea. In this case it would be easy to look for a '/' in the description and then loop around adding the labels and textboxes for, let's say, 'Tiramisu 4p' & 'Tiramisu 12p' when the Product 'Tiramisu 4p en 12p' is found (Prime example there - this product does not conform to the suggested format of 'Tiramisu 4p/12p' and there's a few more like that while others use a format like ' Fruit croutte 4/6/8/10/13') so, generally, the usual suggestion would be to have those as separate products in their own right.

    If you want to continue this, I'll post some basic code to get you started but it really is not that complicated, just a matter of knowing what is is you can do with VBA and how to manipulate controls.

    Leave a comment:


  • Allround_IT_er
    replied
    To put the file in attachment

    Leave a comment:


  • XenoCode
    replied
    Forgot what?

    Leave a comment:


  • Allround_IT_er
    replied
    I forgot ...
    Attached Files

    Leave a comment:


  • Allround_IT_er
    replied
    I'm Impressed!
    It's working exactly as I wanted.
    I spent so many hours already to try making it work with no result.
    You can do this in a matter of an hour, wow!

    Now, I adapted the multipages, because there are only three departments.
    Departerment 1 (Brood) has 5 frames
    Departement 2 (Boterkoeken) has no or only 1 frame (The frame could be called the same as the name of the department (Boterkoeken))
    Departement Patisserie has 7 frames.

    There is a problem with the third department. You can see the name of the product in the sheet but every time there is a number involved, it means it can be ordered for a different number of persons (2,4,6,8, etc).

    How can I arrange that on the frame? It is about "cakes". So, a client can order 6 cakes: 2 for 4 persons, 2 for 6 persons and 2 for 8 persons. How to make place on the userform to make it possible to register the order correctly?


    I tried to adapt your code for the second department "Boterkoeken" (Buttercakes) by replacing "Brood" by "Boterkoeken" but that didn't work.

    I realize now that it is more complicated as I thought, you are absolutely right about that :-)

    Leave a comment:

Working...
X