Automating a Custom Label Maker using VBA in Excel

Warehouse Label Maker

For over a decade a medical supplies warehouse has stocked group homes of all their tenants over the counter medical supply products. Since each there were so many boxes with each order, every box needed a custom label so clients knew whose monthly supplies were whose.

Every sheet of labels was made of 10 columns by 3 rows and each label was formatted in the following layout:

 

Rectangle: Rounded Corners: Long Term Care Group Home Name Medical Patient Name 1 - 2

 

 

 

Rectangle: Rounded Corners: Long Term Care Group Home Name Medical Patient Name 2 - 2                                                                                                                                          

 

 

 

 

The 1-2 stands for 1st box of 2 boxes with this order and 2-2 stands for the last box. One house would have up to 25 customer orders and each customer has 9-35 boxes on average. The process of writing or typing each label individually was costing a total of 8 man hours per day on average and often items were mislabeled due to ever increasing volumes of orders.

Implementing a friendly user interface

                The user interface for this situation ended up being most efficient by utilizing a table for the warehouse employee to fill out and a button to trigger a macro to make labels.

 

Instead of handwriting each label, automating the labels saved an exponential amount of time. Whether a customer order has 2 or 52 boxes, it always took the same amount of time to make the labels as if the customer had only one box. The result of the Make Labels button populates Sheet1 with labels ready to print.

The company also benefited from a decrease in mistakes from sloppy handwriting or miscounting. By exporting a log of these entries into a database, the company was able to better forecast inventory supply orders ultimately allowing the warehouse to decrease backorders.

 

The VBA Label Maker Code

In this example a lot of customization code was implemented to fit the needs of the business. For the sake of simplicity, the bare bones of using VBA to make dynamic labels is supplied here.

There are two pieces to this code. This first code will apply the label formatting on Sheet1 to get the dimensions set. This macro can be placed in a regular module to be ran.

Text Box: Sub LabelMaker_Start() ' converts a single column of data into multiple columns of data formated for labels Sheets(“Sheet1”).activate ‘The macro calls a procedure located in the Sheet1 worksheet Application.Run thisworkbook.name & "!Sheet1.ColtoRows" Cells.Select Selection.RowHeight = 75.75 Selection.ColumnWidth = 34.14 With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = True .IndentLevel = 0 .ShrinkToFit = True .ReadingOrder = xlContext .MergeCells = False End With With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = True .IndentLevel = 0 .ShrinkToFit = True .ReadingOrder = xlContext .MergeCells = False End With End Sub

The second portion of VBA code is called at beginning and is located in Sheet1.

Text Box: Sub ColtoRows() Dim RNG As Range Dim i As Long Dim j As Long Set RNG = Cells(Rows.Count, 1).End(xlUp) j = 1 On Error Resume Next nocols = InputBox("Enter Number of Columns Desired", "Number of columns across label sheet", "3") For i = 1 To RNG.Row Step nocols Cells(j, "A").Resize(1, nocols).Value = _ Application.Transpose(Cells(i, "A").Resize(nocols, 1)) j = j + 1 Next Range(Cells(j, "A"), Cells(RNG.Row, "A")).ClearContents End Sub

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Automating an Excel Workbook to Install an Add-In to Excel

Using VBA to add an Add-In

I was assigned a task to update an outdated Excel Add-In VBA project so the office could upgrade their version of Microsoft Office. After updating the code, part of my job was implementing a strategy to batch release the updated Add-In. My client requested a way of doing this so new employees could use the Add-In application without having security prompts and without changing the security settings. This is the Enable Editing warning that pops up each time the user opens a new workbook from the new Add-In Toolbar:

Batch release an Add-In update

One way to batch release the application update without changing security settings is to request they add it to their trusted files. This will put the file in a special trusted folder and suppress the Enable Editing warning message thereafter. Another way is to automate this choice and have the Add-In Ribbon Toolbar be able to run macros for an application without any user input on any security setting. For Excel 2007 and later, each excel user has a special Add-Ins folder. This is the file path that opens when hitting browse in Excel-Addins. In Windows 10 the file folder path looks like this:  C:\Users\UserName\AppData\Roaming\Microsoft\AddIns

Pull quote

Some Things to Remember about Excel Add-Ins

·         An Add-In file is the same as a .xlsm file but is saved as .xlam

·         .xlam files differ in that they do not have visible worksheets for the user

·         An Add-In is a VBA project that contains the code to utilize an application

VBA Code to automate this process is in the Workbook Module of the .XLSM file

                The Workbook Module controls the code that will trigger upon a predetermined event by the workbook. This is common for running code when a workbook opens or closes. We want the .xlsm file to check to see if the user has the Add-In and if they don’t, prompt the user to install the Add-In. From there the .xlsm is no longer needed and can be closed. The next time excel is opened, your Add-In will be displayed as an available Add-In in the Excel-Addins menu. Activating the Add-In, in my case, turns on the custom toolbar tab for the application.

 

In the workbook module of the macro file you are turning into an add-in, enter the following code:

Pull quote
Each time the workbook is opened, the procedure to check for the updated Add-In will occur. If the Add-In is not found, the user is asked to install it with a command prompt. If the user selects yes then the sub routine SaveWorkbookToAddinsCollection will be triggered. This macro dynamically installs the .xlsm file running the code as a .xlam file into the trusted Add-Ins folder. This macro can go into the workbook module as well.

Pull quote

Successful installation walk-through

                In my project, the Add-In was an application called JEM that is controlled via a new Ribbon tab named JEM in the ribbon toolbar.

                Once the workbook is opened, the user will see a prompt for installing the updated Add-In.

                After the installation, the user can activate the new Add-In from the Excel AddIns Menu.

                Workbooks will display the newly updated ribbon tab after pressing ok and will not receive pesky Excel messages to run the application’s code.