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

    Didn't know which frame you wanted to add the products to (My Dutch is not the best - My Dutch is non-existent!) but wonder why you would add a variable amount of information to a label. This would be better suited to a ListBox.
    As an example, your workbook with a listbox added. It is populated with the Fixed Order items for the selected customer in the drop-down.

    Thx Xenocode. The listbox works fine.
    The idea behind this is, that the person who takes the orders has no knowledge of excel at all.
    That's why I thought on a userform with labels and textboxes. If the boss adds a product it automatically should appear on the userform without changing the code.
    On the sheet "Brood", column B marks the the frame where the product should go.
    Maybe a listbox will work to, but how do I add the amount? I thought that labels and textboxes are quicker. Now you'll have to scroll the listbox and search for the product.
    The idea is that the customer is called every day and tells what he needs through telephone. The person behind the excel sheet (the caller) marks what the customer wants and how much.
    Once the order is placed, the order should be saved and he should be able to print every departement (= the name of the multipages) individually.
    So it is a concept, we thought it should work faster with labels and textboxes. With listboxes he would has to select every product the customer wants, be able to add the amount and then go to the second product.
    I think that would work much slower then just adding the amout next to the label in a textbox.


    So first I'll have to check if in the worksheet "Brood" (=Bread) the value equals "Op steen gebakken" (Baked on stone. = column B on the worksheet). If yes than the label.name on frmOpsteengebakken should take that value but only when on the worksheet "Vaste bestelling" (fixed order) the value equals "y". There are 15 labels on frmOpsteengebakken. If only 4 products equals "y" for that customer, the other 11 labels should be invisible.


    So I would like to combine following code:


    Code
    For i = 1 To 15
    If Worksheets("Brood").Cells(i, 2).Value = "Op steen gebakken" And Not IsEmpty(Cells(i, 2).Value) Then
        Me.frmOpsteengebakken.Controls("LblBroodSoort" & i).Caption = Worksheets("Brood").Cells(i, 1).Value
    Else
        Me.frmOpsteengebakken.Controls("LblBroodSoort" & i).Caption = ""
        Me.frmOpsteengebakken.Controls("Textbox" & i).Visible = False
    End If
Next i


    Code
    For iOsg = 1 To 15
''    If Sheets("Vaste bestelling").Range("C2") = "y" Then
''   If Sheets("Vaste bestelling").Range("A2") = cboKlanten.Value Then
'   Me.frmOpsteengebakken.Controls("LblBroodSoort" & iOsg).Caption = Worksheets("Brood").Cells(iOsg + 1, 2).Value
''End If


    I tried to combine but the two for .... next loops just seems not to work ....

    It's Saturday... Do you really expect brains to work as well??? :)


    I'm sorry, I still don't really understand what you want to do. If you need to edit something, then the ListBox is not a good choice but that wasn't clear from your first post.


    At the same time, the fact you are hard coding things means that "If the boss adds a product it automatically should appear ...' also won't work without changes to the code by somebody (probably you, and that might take time from your Saturdays). I would think that code that automatically adapts to changes would be a good thing as well.


    You have 3 sheets listing Bread, Butter cookies & Cakes/Pastries. You also have a 'Best Seller/Frequently Ordered sheet.


    Fred calls up to place an order so whoever answers goes and selects Fred from the dropdown list. Starting with bread, you want all lines usually ordered by Fred to appear in the relevant frame - Stone-baked, Square, Speciality, Soft etc... along with an entry box for quantities. I suppose you also need some way to add additional input boxes.


    Is that a rough idea to start with?

    Your title says 'Dynamic labels...'.


    Your UserForm1 slightly reworked to include dynamic labels (and textboxes and scrollbars if needed). Pick a customer from the dropdown and the Various breads (only at this stage) in that customers frequently ordered list are populated to the various frames.


    Not meant to be a working example (It only loads breads, no saving either) but an example of how you could work it.


    And apologies, it is a little more complicated than your original 15 lines or so posted.

    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 :)

    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: [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

    [tr]


    [TD="width: 107"]Brood[/TD]
    [TD="width: 64"]Op steen gebakken[/TD]

    [/tr]


    [tr]


    [td]

    Brood

    [/td]


    [td]

    Carré brood

    [/td]


    [/tr]


    [tr]


    [td]

    Brood

    [/td]


    [td]

    Speciallekes

    [/td]


    [/tr]


    [tr]


    [td]

    Brood

    [/td]


    [td]

    Zachte luxe

    [/td]


    [/tr]


    [tr]


    [td]

    Brood

    [/td]


    [td]

    Suggestie

    [/td]


    [/tr]


    [tr]


    [td]

    Boterkoeken

    [/td]


    [td]

    Boterkoeken

    [/td]


    [/tr]


    [tr]


    [td]

    Patisserie

    [/td]


    [td]

    Drooggebak klein

    [/td]


    [/tr]


    [tr]


    [td]

    Patisserie

    [/td]


    [td]

    Drooggebak groot

    [/td]


    [/tr]


    [tr]


    [td]

    Patisserie

    [/td]


    [td]

    Cake

    [/td]


    [/tr]


    [tr]


    [td]

    Patisserie

    [/td]


    [td]

    Mousse/Slagroom

    [/td]


    [/tr]


    [tr]


    [td]

    Patisserie

    [/td]


    [td]

    Individueel/punt

    [/td]


    [/tr]


    [tr]


    [td]

    Patisserie

    [/td]


    [td]

    Dessert

    [/td]


    [/tr]


    [tr]


    [td]

    Patisserie

    [/td]


    [td]

    Boterkreem

    [/td]


    [/tr]


    [/TABLE]

    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.

    I don't quit understand:

    Quote

    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:

    BroodCarré brood
    BroodSpeciallekes
    BroodZachte luxe
    BroodSuggestie
    BoterkoekenBoterkoeken
    PatisserieDrooggebak klein
    PatisserieDrooggebak groot
    PatisserieCake
    PatisserieMousse/Slagroom
    PatisserieIndividueel/punt
    PatisserieDessert
    PatisserieBoterkreem


    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 :)

    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.

    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?

    "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 :)

    Quote

    "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...

    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).

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login