Macro To Print Labels

[YuriyBaron]

Hi,

I need VBA code for the attached spreadsheet. The spreadsheet has 3 tabs: Tables, MoveTags, Label.
Tables Tab generates data automatically by using SQL and the data from A1 on the MoveTags Tab.
MoveTags has the button that needs to ask for input. The button and UserForm are created.
The label tab is the one that needs to be printed.

On the attached spreadsheet, the total amount of labels that need to be printed is 14. This is because there are a total of 3 SubNo (0,1,2) and each SubNo has a RSC (SubNo 0 has 6, SubNo 1 has 4, SubNo 2 has 4)
All of this will change once a different Base ID is entered.

So once you click on the button and enter the Work Order, in this case W157772, and then hit print the following needs to happen:
The Label Tab needs to be filled out with the information from the Tables Tab. Like the attached pictures. This needs to be done 14 times and printed on the zebra label printer that on the local network.

Label 1-6 will have Part No. 29502066-14
Label 7-10 will have Part No. 29502066-14A
Label 11-4 will have Part No. 29502066-4B

In a different post, I asked for assistance printing to Zebra printer, but I think that the post died.
https://www.excelforum.com/excel-pro...ml#post4962415

Your help on this is greatly appreciated.
Thanks

[jason.b75]

Duplicate / follow up to https://www.excelforum.com/excel-pro...ml#post4962415

Thread merge requested.

[jason.b75]

Looks like the mods didn't agree with my request to merge your threads so I'll continue here.

The following are not clear from your sample, please clarify, with more samples if needed.

Will part number always be taken from sub ID 0 in the left table for sub ID's 0,1 and 2 on the labels?
Similarly, if any field is blank in the tables, should the label be blank, or information taken from elsewhere? (as appears the case with part number in your sample).

I assume next op will always be same sub ID.

Should the fields shown as 'Blank' be left empty for completion after printing?

Which format should we use? The label sheet in the attached file is not the same as the screen captures.

What should go in the 'Leg no' box?

Will the table names always be the same, or will they change with the next data import?

There may be more to clarify, but that will get us started.

[YuriyBaron]

The part number is dependent on Sub ID.
In the example, Sub ID numbers that are 0 belong to part # 29502066-14
All Sub ID numbers that are 1 belong to part # 29502066-14A
All Sub ID numbers that are 2 belong to part # 29502066-4B
So yes.

There should not be any blank fields on the label except for Quantity, EMP No., and Date. These three are always blank.
Each Sub id that is not 0 will have a part no that is located in column "G"; Sub ID 0 will always have a part number located in column "E".

"I assume next op will always be same sub ID." - This is where things get complicated and beyond my macro capabilities.
I need a label for every OP (Operation) listed in the table that's on the right. OP (Operation) = RSC column.
I this example, there are a total of 14 RSCs so I will need to print 14 labels. So there needs to be a loop macro for the labels to be generated and printed.

"Which format should we use? The label sheet in the attached file is not the same as the screen captures."
Use the new attached spreadsheet. I created pictures to explain better what needs to happen.

"What should go in the 'Leg no' box?" - Leg No. = Sub_ID = SubNo

"Will the table names always be the same, or will they change with the next data import?" - The names of the tables and layout will always remain the same. The quantity of data will change.

Table Legend:
Row_ID - Do not use
Create_Date - Do not use
Base_ID = base = W.O.
Sub_ID = subNo = Leg No.
Part_No - Unique for each Base_ID & Sub_ID
Part_Description - Do not use
Leg_Drawing_No - Unique for each Base_ID & Sub_ID
seq - Do not use
rsc (resource) = OP (Operation)
Date - Leave blank
Quantity - Leave blank
Emp No. - Lave Blank


I have attached a new spreadsheet with a new VBA code.
As always, thank you for the help!

Private Sub TextBox1_Change()

Worksheets("Label").Range("J4").Value = UCase(TextBox1.Value)
Worksheets("MoveTags").Range("A1").Value = UCase(TextBox1.Value)

End Sub
Private Sub CommandButton1_Click()

Dim ws As Worksheet
Dim lr As Long
Dim wo, Box As String
   
Set ws = ThisWorkbook.Worksheets("Tables")              ' ws = Work Sheet
lr = ws.Cells(Rows.Count, 13).End(xlUp).Row             ' lr = Last row

Label2.Caption = "Total number of labels that will be printer is = " & lr - 1

Debug.Print lr, wo

Box = MsgBox("Are you sure you want to print " & lr - 1 & " label(s) " _
& "?", vbQuestion + vbYesNo)
If Box = vbNo Then
Exit Sub
Else: ThisWorkbook.Worksheets("Label").PrintOut ActivePrinter:="\\msp-dc-001\MSP-Label2 on Ne07:"
End If

End Sub

[jason.b75]

Give this a try to make sure it does as needed on the layout of the labels.
This will not print, but simply show each label on the sheet with a messagebox to pop up after each one showing how many have been done.

Once we are sure that this is correct then I'll add the print code to it.

You will need to make some changes to the layout of the label, the part number is too big to fit in the cell.

Both of the workbooks that you attached had the same format on the label, with the space for current op missing, so I've used the format from the screen captures as the basis for the code instead.

Option Explicit
Sub label_test()
Dim c As Range, ctr As Long
ctr = 1
    For Each c In [Table_Query_from_Visual654[base]]
        With Sheets("Label")
            .[J4] = c.Value
            .[J22] = Intersect(c.EntireRow, [Table_Query_from_Visual654[rsc]])
            .[AK4] = Intersect(c.EntireRow, [Table_Query_from_Visual654[subNo]])
            If Intersect(c.EntireRow, [Table_Query_from_Visual654[subNo]]) = 0 Then
                .[J9] = Application.VLookup(Intersect(c.EntireRow, [Table_Query_from_Visual654[subNo]]) & "", [Table_Query1[[Sub_ID]:[Leg_Drawing_No]]], 2, 0)
            Else
                .[J9] = Application.VLookup(Intersect(c.EntireRow, [Table_Query_from_Visual654[subNo]]) & "", [Table_Query1[[Sub_ID]:[Leg_Drawing_No]]], 4, 0)
            End If
            .[J25] = IIf(Intersect(c.Offset(1).EntireRow, [Table_Query_from_Visual654[subNo]].EntireColumn) = Intersect(c.EntireRow, [Table_Query_from_Visual654[subNo]]), Intersect(c.Offset(1).EntireRow _
                , [Table_Query_from_Visual654[rsc]].EntireColumn), "")
        End With
        MsgBox "label " & ctr & " of " & [Table_Query_from_Visual654[base]].Count
        ctr = ctr + 1
    Next
        
End Sub

[YuriyBaron]

That code works perfectly!
I modified the cells and added the field for Current OP.
New file attached.

[jason.b75]

I've added print functionality to the code, and thrown in a slight twist. Given the problems we had getting the printer active, I've utitlised the existing code in Module 1 to search for and assign the printer with "Label2" somewhere in the name.

As a backup, if it can't find the correct printer, it will warn you of this and give the option to abort before printing in the wrong place.

Add this code after the existing code in module 1 and assign it to the print button. You don't need your userform now, everything is done by this code.

Sub Print_Labels()
Dim c As Range, Printer As Variant, Printers As Variant, cPrinter As String, AbortPrint
If MsgBox("Are you sure you want to print " & [Table_Query_from_Visual654[base]].Count & " Labels for " & [Table_Query_from_Visual654[base]].Cells(1, 1) & " ?", vbQuestion + vbYesNo) = vbYes Then
Application.ScreenUpdating = False
    cPrinter = ActivePrinter
    Printers = GetPrintersAndPorts
    
    For Each Printer In Printers
        If InStr(Printer, "MSP-Label2") Then
            Application.ActivePrinter = Printer: Exit For
        End If
    Next Printer
    
        If Not InStr(ActivePrinter, "MSP-Label2") Then
            If MsgBox("Specified Label printer not found!" & vbCrLf & "Do you want to  print to " & ActivePrinter & " ?", vbExclamation + vbYesNo) <> vbYes Then
                GoTo AbortPrint
            End If
        End If
     
    For Each c In [Table_Query_from_Visual654[base]]
        With Sheets("Label")
            .[J4] = c.Value
            .[J22] = Intersect(c.EntireRow, [Table_Query_from_Visual654[rsc]])
            .[AK4] = Intersect(c.EntireRow, [Table_Query_from_Visual654[subNo]])
            If Intersect(c.EntireRow, [Table_Query_from_Visual654[subNo]]) = 0 Then
                .[J9] = Application.VLookup(Intersect(c.EntireRow, [Table_Query_from_Visual654[subNo]]) & "", [Table_Query1[[Sub_ID]:[Leg_Drawing_No]]], 2, 0)
            Else
                .[J9] = Application.VLookup(Intersect(c.EntireRow, [Table_Query_from_Visual654[subNo]]) & "", [Table_Query1[[Sub_ID]:[Leg_Drawing_No]]], 4, 0)
            End If
            .[J25] = IIf(Intersect(c.Offset(1).EntireRow, [Table_Query_from_Visual654[subNo]].EntireColumn) = Intersect(c.EntireRow, [Table_Query_from_Visual654[subNo]]), Intersect(c.Offset(1).EntireRow, [Table_Query_from_Visual654[rsc]] _
                .EntireColumn), "")
        End With
            ThisWorkbook.Worksheets("Label").PrintOut
    Next
AbortPrint:
    Application.ActivePrinter = cPrinter
    Application.ScreenUpdating = True
End If
End Sub

Hope this helps.

[YuriyBaron]

I have been using this for a little over a week now and it works exactly like intended, so thank you for that.
I was wondering if you could modify this code a bit to incorporate the following:
1. Skip printing anything that has the words "LAYOUT" and "MATERIALS GROUP" in column P of the Tables Tab.
2. Specify which Sub_ID/SubNo to print (if left blank, print all). On MoveTags tab I created a space for Sub_ID/SubNo. This is the Column N on Tables Tab.

New spreadsheet attached.

Thank you very much for all your help on this.

[nishanfaith]

Hi folks,

Thank you so much for sharing your knowledge here. I need to use the VBA code to print a barcode on 1 inch x 1.5inch label. I would like to know how to do that?

Thank you so much if someone can help me out to fix this issue.

[Arkadi]

nishanfaith,

You need to start your own thread, not continue someone else's. This thread was done in 2018

[AliGW]

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread