Solved How to Use Dymo Label in my VBA

May 24, 2017 at 05:14:52
Specs: Excel Macro
Hi,

I need to create a macro that is related to a button which highlights the selected cells in yellow and that by printing on that same button, it prints my selection on my Dymo labelWriter 450.

Now I have my macro for the highlighting. My problem is that i want to print my selection which is for example A1,B1,C1 but i want c1 to be written uder the A1 and B1. Its for an event and i want the name of the person and its restaurant name right Under.

I dont know how to do this and its been several days I,m trying.

Can someone help me please :) ?


See More: How to Use Dymo Label in my VBA

Report •

#1
May 24, 2017 at 06:34:38
Ok, I may have over complicated this but right now this is my solution.

What you will need to do is

On another sheet (Sheet 2 in my case)

Center justify range A1
Center justify range B1
Merge and center range A1 - B1
Select range A1 through to B2 and set this as your print area 'Page Layout > print Area > set print area'
-Under control panel printers make sure your label printer is set as default printer.

Now here is the code.

You will have to change the reference to sheet2 to whatever sheet you are using. Now you will need to select three cells (assuming this is the number of cells you need to extract) you can use the ctrol key to select multiple cells, by holding ctrl and clicking on each cell. Then press you macro button (obviously your button will need to call the code below)

This will then capture the three cells and put them into sheet 2 into their respective places, and because these cells are centered or merged the restaurant name will appear bellow name of the person

hope this makes sense?


Sub PrintSelection()

    ' Declar eall the variables
    Dim bcell As Object ' Variable for counting each cell in the selection
    Dim ColItem(2) ' Array to hold all selected items
    Dim i As Integer ' Counter for adding items to array
 
    ' Clear the contents of sheet 2
    Sheets("Sheet2").Cells.ClearContents
    
    ' Set counter to zero so that we can add the first item
    ' to position zero of the array
    i = 0
    
    ' Loop through each item selected and add it to the array
    For Each bcell In Selection
        
        ColItem(i) = bcell
        
        i = i + 1
        
    Next bcell
    
    ' Exact the array items into cells in sheet to
    Sheets("Sheet2").Range("A1") = ColItem(0)
    Sheets("Sheet2").Range("B1") = ColItem(1)
    Sheets("Sheet2").Range("A2") = ColItem(2)
       
    ' Print sheet 2 to the default printer
    Sheets("Sheet2").PrintOut
    
End Sub

message edited by AlwaysWillingToLearn


Report •

#2
May 30, 2017 at 08:50:55
✔ Best Answer
re: "-Under control panel printers make sure your label printer is set as default printer."

Another option is to capture the "normal" default printer, print the labels to the Dymo device and then reset the default printer, all within the macro. No need for a manual change each time you want to print a label.

Try replacing this section of AWTL's fine code...

    ' Print sheet 2 to the default printer
    Sheets("Sheet2").PrintOut

...with this:

'Print to Dymo Label Printer and go back to the original printer when done.
  originalPrinter = Application.ActivePrinter
     Application.ActivePrinter = "*** Insert Dymo Printer Name Here ***"
     Sheets("Sheet2").PrintOut
  Application.ActivePrinter = originalPrinter

To grab the system's name for the Dymo printer, you can follow these steps once:

1 - Set the default printer to the Dymo device
2 - Open a new workbook
3 - Run this code

Sub GetDymoName()
 Range("A1") = Application.ActivePrinter
End Sub

4 - Copy the string from A1 and paste it in place of *** Insert Dymo Printer Name Here *** in the code above, making sure that it is between the quotes
5 - Reset the default printer to the original default

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#3
June 1, 2017 at 03:02:07
Very good suggestion Derby thanks for that :)

Report •
Related Solutions


Ask Question