Print selected area only with VBA code

[Jagat Pavasia]

Dear sir,
I have drop down list of 3 range and result in A3. ( it is already done)

Now, for example if I select name "Mansukhbhai" and then I tick mark in check box on both address and invoice
then I will get 2 print in my brother label printer. ( the print should only with "Mansukhbhai" labeled)

if I tick only in Address in check box then print only one label.
if I select both tick mark then two label print should come out from printer.


i have attached screenshot of it.

please find it and help me

 

[DanteAmor]

Put ALL of the following code in a module and run the macro "Print_Selected_Area"

Sub Print_Selected_Area()
  With ActiveSheet
    If .CheckBox1.Value = False And .CheckBox1.Value = False Then
      MsgBox "Select checkbox", vbCritical
      Exit Sub
    End If
    .PageSetup.PrintArea = ""
  
    Select Case LCase(Range("A3").Value)
      Case LCase("Mansukhbhai")
        If .CheckBox1.Value Then Call Print_Label("A10:C18")
        If .CheckBox2.Value Then Call Print_Label("E10:G18")
        
      Case LCase("Jagdishbhai")
        If .CheckBox1.Value Then Call Print_Label("A21:C30")
        If .CheckBox2.Value Then Call Print_Label("E21:G30")
        
      Case LCase("Ashwinbhai")
        If .CheckBox1.Value Then Call Print_Label("A33:C41")
        If .CheckBox2.Value Then Call Print_Label("E33:G41")
        
      Case Else
        MsgBox "Select a name", vbCritical
    End Select
  End With
End Sub

Sub Print_Label(sRng As String)
  With ActiveSheet
    .PageSetup.PrintArea = sRng
    .PrintOut
  End With
End Sub

 

[mmhill]

How you read the value of the checkboxes depends on the type of control you are using - Form Controls or ActiveX controls. It also wasn't clear if "Mansukhbhai" and the rest were named ranges.

The easiest and most flexible way to do this is to use named ranges. Select the address and invoice areas you want (address area first, hold CTRL and select the invoice area) and then type the name you want in the address box in the upper left like the image. You can do this for as many names as you want and will never have to edit your code.

For the VBA below, I named the checkboxes "Address" and "Invoice". If you want, you can add "preview:=True" to the end of each line.

If you are using Form controls

Sub PrintStuff_FormControls()
    With ActiveSheet
        If .Shapes("Address").ControlFormat = xlOn Then .PageSetup.PrintArea = .Range(.Range("B3")).Areas(1).Address: .PrintOut
        If .Shapes("Invoice").ControlFormat = xlOn Then .PageSetup.PrintArea = .Range(.Range("B3")).Areas(2).Address: .PrintOut
    End With
End Sub

If you are using ActiveX controls

Sub PrintStuff_ActiveX()
    With ActiveSheet
        If .Address.Value Then .PageSetup.PrintArea = .Range(.Range("B3")).Areas(1).Address: .PrintOut
        If .Invoice.Value Then .PageSetup.PrintArea = .Range(.Range("B3")).Areas(2).Address: .PrintOut
    End With
End Sub