/
Job sheet - BUZ 3 to BUZ Cloud

Job sheet - BUZ 3 to BUZ Cloud

Owned by Alex Morrisson
May 07, 2021

Changes to Inventory Workbooks for BUZ 3 to BUZ Cloud "BOD"

Job Sheets need to be edited for BOD and can be saved as XLSM. BOD allows downloading of Job Sheets as PDF or XLSM(Excel). XLSM is mainly for making changes to the template that is used to generate the PDF.  

Changes that need to be made are:

  1. Many old Job Sheet templates used functions that are no longer supported this is typically found when the sheet is opened for the first time and goes to an error in the VBA. Even if you don't get an error it best to check what are called the references. To check this on the VBS screen select Tools > References and there should only be 3 items ticked, Visual Basic for Applications, Microsoft Excel 15.0 Object Library and Microsoft Forms 2.0 Object Library untick any others. The numbers may vary as it depend of the version of Excel you are using. It should look like this
  2. .
  3. The BUZ data is now on it's own tab called Data.

    1. Add a new worksheet (tab) name it Data

    2. Add to the Data sheet the DataSettings named area (see Using Excel Name Manager below). The table below can be copied and pasted into the Data sheet as a template, we suggest at A5. This is required to resize the form when there are more items than the current number of lines in the template and to indicate which worksheets (tabs) to include in the PDF.

      DATASETTINGS
      Sheet NameFirst RowLast RowRight ColumnInclude in PDFNo to Print
      Job Sheet1120AITRUE1
      Pelmets1221TTRUE2
      Labels1120KFALSE1
      Components413ASFALSE0
    3. This is a table of all the worksheets in the Job Sheet.
      1. First Row is where the first items detail is to be inserted.
      2. Last Row is where the last item can be inserted in the standard form. That is the row the form needs to expand from if the are more items than the standard form allows.
      3. Right Column limits the number of columns to be extended down, lookup tables should be to the right of this column.
      4. Include in PDF, only sheets with TRUE will be included and the number of copies as per the No to Print.
    4. The next step is to Cut and Paste the data area from the Job Sheet tab to the Data tab. To do this select the Job Sheet worksheet, then select columns AV to DS and select Cut.
    5. Select the Data worksheet, select cell AV1 and choose Paste. Now all the formulas that referenced that data will change to referencing the Data worksheet. You may find it helpful to select columns H to AU, right click and Hide then the Data tab should look similar to the image below.
  4. Add Labels worksheet if labels are required and Edit Links (Data Ribbon) Change Source to this XLS by choosing it's file in the Open Window.
  5. Order No and Revision need to be equated to Data!BC5 & Data!BD5
  6. In the VBA (Alt-F11) clear all text in ThisWorkbook it should look like this before it's cleared
  7. Delete ALL modules except Main and add BODFunctions module.
  8. Set position of barcode, on each worksheet that requires it, by adding =BarcodePosition("K",1) in the left most cell of the area where barcode image is to be placed where K1 the Left most cell's cell reference.
    NB : NOT like =BarcodePosition("K1")
  9. Save the workbook.
  10.  Go to the Inventory Group in BOD Settings > Inventory Settings > Groups, scroll down to Job Sheet heading, click Select file and choose the XLSM and click Upload

Setup of "Tickets" Job Sheets

Door Tickets (1 per page)

Your Tab name needs to be "Tickets" plural

Set print area on the first Ticket only for item 1, have a second ticket setup with all formulas but relating to item 2.

Each formula will contain "INDIRECT", This allows the ticket to go down the page rather than across the page as BUZ is already programmed to do.

For the first ticket cell "A1" will contain the item number

Using the below formula returns a reference in the cell you require in this case the "Item number"

=INDIRECT("'Data'!BN"&(10+A1))

The data settings should be set to your first printable area,even though you have the formulas in for the second item.

The below examples show

First row 1

Last row 33

Right column U


Download Job Sheet template/example here → JobSheets_SECD_BUZ_(1).xlsm


Job Sheet