• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro Button to Set Print Area

nathanthomson11

New Member
I have a tracking file which generates a new barcode on each line-item as a new line is created. For example, I am entering data on line 3458, a barcode is generated on BE3458. I now want to create a macro button on BF3458 which I can click and it will automatically adjust the print area to BE3458 and print only the new barcode.

I could use the following code but there are a lot of entries on this sheet so I would need to enter it thousands of times and link to each button:

Sub PrintSomeCells()
Range("BE3458").PrintOut
End Sub
 
How is barcode generated? Is it via font (i.e. value is typed in), code or formula?

If it's done by font. Something like below. This goes in the worksheet module.
Code:
Private Sub BttnPrint_Click()
Dim cellPrint As Range, PrintBarCode As String
With Shapes("BttnPrint")
    Set cellPrint = .TopLeftCell.Offset(, -1)
    .Visible = msoFalse
End With
PrintBarCode = MsgBox("Do you want to print the barcode?", vbQuestion + vbYesNo)
If PrintBarCode = vbYes Then cellPrint.PrintOut
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("BE:BE")) Is Nothing Then
    With Shapes("BttnPrint")
        .Top = Target.Offset(, 1).Top
        .Left = Target.Offset(, 1).Left
        .Visible = msoTrue
    End With
End If
End Sub

You will need to add ActiveX CommandButton control to the sheet.

See attached sample. Button is hidden, but will appear if you confirm a value in any cell in column BE.
Note, columns A to BD are hidden in the sample.

If you need further help. I'd recommend uploading sample workbook.
 

Attachments

  • Sample_ActiveX_Control.xlsb
    20.4 KB · Views: 11
Back
Top