Announcement

Collapse
No announcement yet.

Dynamic labels on a userform with multiple conditons

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

  • 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

  • #2
    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.
    Attached Files

    Comment


    • #3
      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 ....

      Comment


      • #4
        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?

        Comment


        • #5
          It is. Thx

          Comment


          • #6
            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.
            Attached Files

            Comment


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

              Comment


              • #8
                I forgot ...
                Attached Files

                Comment


                • #9
                  Forgot what?

                  Comment


                  • #10
                    To put the file in attachment

                    Comment


                    • #11
                      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.

                      Comment


                      • #12
                        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 :-)

                        Comment


                        • #13
                          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

                          Comment


                          • #14
                            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?

                            Comment


                            • #15


                              "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

                              Comment

                              Working...
                              X