Posts by XenoCode

Dim wbToUse as Excel.Workbook
Dim wbToTest as Excel.Workbook


For each wbTest in Application.Workbooks
   If wbTest.Name Like "WhatEverName*" Then
      Set wbToUse = wbTest
      Exit For
   End I
Next


' Now check if the name was found
If wbToUse is Nothing then MsgBox "Not found", vbExclamation:Exit sub


' Rest of code

Very rough. If finding a workbook is used in multiple places in your code then turn it into a function.

Sorry - I don.'t understand.

You declare a variable as type X (Workbook is this case)
You assign a workbook object to the variable if the test for wb.name like "whatever*" is True
You use that variable to refer to your workbook in code.

Maybe if you post your code.

Use the LIKE operator while looping the workbooks collection

' But just pseudo code
For each wb in Application.Workbooks
   ' You must terminate the partial name with *
   If wb.Name Like "WhatEver*" then
      Got it, do something. Maybe assign to a variable
      Exit for - maybe. You may want to continue the loop to get a list of all like.
   End if
Next

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

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

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

"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

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.

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

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

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

[/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.

Forgot what?

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.

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?

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.

The Gradient of a cell is an object, not a String or Boolean, so you can query the Typename

Debug.Print Typename(activecell.Interior.Gradient)
LinearGradient

or the actual existence of such an object

Debug.print activecell.Interior.Gradient is nothing
False

My apologies for mentioning a 'Property' ... sometimes distinctions get blurred.

You already have code but as not posted a very quick comment:

You can check if a cell has a gradient fill by simply querying the Interior.Gradient property. It will return Nothing if no gradient or the type of gradient ('LinearGradient') if there is.

You get the last used row in Column 1, you write to column 2.

Look closely at how you determine the last used row and the columns you actually write to.

"try to enter data beginning with a minus sign ... it should just prevent the data entry"

VBA cannot monitor input while a cell is being edited, only after the edit has been committed by pressing Enter or the user moving the cursor to a different cell so there is no way to prevent entry in the first place.

Best you can do is allow entry and then check but it seems very restrictive to check for a '-', what if the user entered 'x5 cars' or '#$&*$ cars'??

Or maybe PasteAppendTable expects an existing table in Word to PasteAppend to? You haven't said what is in the document.

This should work to simply paste the copied cells rather than inserting into a table. But without copies of your document/workbook all this is guesswork

With oDoc
      If .BookMarks.Exists("RISK") Then
         .BookMarks("RISK").Range.Characters(1).Select
         ' Selection is a property of Word, not the document
          objWord.Selection.Paste
      Else
         MsgBox "The document " & UCase(.Name) & " does not contain a bookmark named 'Risk'", vbExclamation, "Error"
      End If
   End With

"I think its on this line...."

I'm sorry, that is not very helpful.

Use the debugger to find out. Edit the error handler

Errorcatch:
     Debug.assert False
     MsgBox err.Description  
     ' This is temporary, if you leave it in it will go into an endless loop so do not forget to remove
     Resume

When the code stops in the debugger use F8 to single step and see where it returns to