Printing Ranges when CheckBoxes = False

Big Lar · Jun 26, 2017

40 ActiveX CheckBoxes reside in Column A of Sheets(“PAYMENTS”)
Each CB corresponds with it’s Row. So, CB4 is displayed on Row 4, CB5 is on Row 5, etc.
CB = TRUE, changes the FONT format for it’s row.

My Macro is called from a Userform CommandButton that performs numerous tasks. The following code is a portion of that Macro.

Prior to Saving the copied Sheet, I wish to print:

Range(“B4:C4”) IF CB4.Value = False
Range(“B9:C9”) IF CB9.Value = False
Etc.

In other words, any unchecked CheckBox will cause that row’s B & C cells to Print.

I’ve failed in every attempt to perform the Print routine, which I believe would fit nicely after Sheets(“PAYMENTS”).Copy

Can I get assistance with the proper syntax to accomplish my desired task?

Rich (BB code):

  'Copy and save Payments Sheet
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
      
   Sheets("PAYMENTS").Copy
   
  ‘I need help here - which I believe is the correct line to run the PRINT routine 

‘With ActiveSheet

‘For Each Checkbox 4 thru 44 = True

‘Find the unchecked CheckBoxes and

‘Print Range B & C Selection

   
   

  MyDateString = Format(ActiveSheet.Range("A1").Value, "yymmdd")
      
      s = ActiveSheet.Range("A2").Text
      d = MyDateString 'ActiveSheet.Range("A1").Text
   ActiveSheet.Name = s & d
    
   
   ActiveWorkbook.SaveAs Filename:=Environ("UserProfile") & "\OneDrive\The ****\ Records 2017\" & d & s & " Payments " & ".xlsx", _
          FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
   
  “More stuff happens after this.

Warship · Jun 28, 2017

Code:

Dim obCB As OLEObject, i As Integer
For i = 4 To 44
    Set obCB = ThisWorkbook.ActiveSheet.OLEObjects("CB" & i)
    If obCB.Object.Value = False Then
        Range(Cells(i, 2), Cells(i, 3)).PrintOut
    End If
Next i

Big Lar · Jun 28, 2017

Warship,
Sincere thanks for the assistance.
I've added your suggested code to my project.
Unfortunately, I get a Run-time error and do not know how to resolve it. I suspect its a missing Reference tool...but, that's just a wild guess!

"Unable to get the OLEObjects properties of the Worksheet class"

Warship · Jun 28, 2017

It may be the Names of the controls are incorrect in my code.

In ThisWorkbook.ActiveSheet.OLEObjects("CB" & i) the ("CB" & i) is the Name of your checkbox control.
I used "CB" because this how you referenced them in your write-up.
If you did not change the Names from the default Name they would be CheckBox1, Checkbox2...

Try changing: Set obCB = ThisWorkbook.ActiveSheet.OLEObjects("CB" & i)
To: Set obCB = ThisWorkbook.ActiveSheet.OLEObjects("CheckBox" & i)

Big Lar · Jun 28, 2017

Warship,
That was it! Plus, I moved the code to an early point in the module. Seems it doesn't like performing the task on a copied sheet.
Many thanks for solving my "CB" vs. "CheckBox" issue with your code.

I’ve added some print formatting to suit my needs. It prints correctly…Except,

When Next i is complete, the Run-time error message appears
“Unable to get the OLEObjects property of the Worksheet class”

Any ideas on resolving this?

Code:

Dim obCB As OLEObject, i As Integer
    
  For i = 4 To 44
      Set obCB = ThisWorkbook.ActiveSheet.OLEObjects("CheckBox" & i)
      If obCB.Object.Value = False Then
      
      
      With ActiveSheet.PageSetup
          .LeftMargin = Application.InchesToPoints(3.5)
          .RightMargin = Application.InchesToPoints(0.25)
          .TopMargin = Application.InchesToPoints(1.5)
          '.BottomMargin = Application.InchesToPoints(0.75)
          '.HeaderMargin = Application.InchesToPoints(0.3)
          '.FooterMargin = Application.InchesToPoints(0.3)
          .CenterHorizontally = True
          .CenterVertically = True
          .Orientation = xlLandscape
          .PaperSize = xlPaperEnvelopeDL
          .FirstPageNumber = xlAutomatic
          .Order = xlDownThenOver
          .Zoom = 165
          .FitToPagesWide = 1
          .FitToPagesTall = 1
          .AlignMarginsHeaderFooter = True
      End With
      
           Range(Cells(i, 2), Cells(i, 3)).PrintOut
      End If
  Next i

Big Lar · Jun 28, 2017

Never mind...On Error Resume Next fixed it!
Thanks again for the assistance.

Warship · Jun 28, 2017

I would try moving your PageSetup outside of the For/Next loop and just before the line "For i = 4 To 44".
PageSetup should be persistent after it is set meaning setting it once should handle all subsequent print commands
Where it now it is running before each printout

Be real careful with "On Error Resume Next".
It is solving your issue but a better method is to resolve the underlying error and fix it.
I see no reason for the error in the code.
It could be a Name issue again, double check all you CheckBoxes to be certain they are CheckBox4 thru CheckBox44
Glad you are running! and you are quite welcome, my pleasure!

Big Lar · Jun 29, 2017

Warship,
You are correct - I had only 43 Checkboxes on the worksheet. (are you looking over my shoulder? haha)

With your code assistance, I’ve moved on to solving another operational issue:

Disabling CheckBoxes where Range(Cells(I, 3)).Value < 0.

However, I get struck with another Run-time error,
Method ‘Range’ of object ‘_Global’ failed

If you don’t mind – what am I doing wrong?

Code:

  Dim obCB As OLEObject, i As Integer
    
  For i = 4 To 44
   
      Set obCB = ThisWorkbook.ActiveSheet.OLEObjects("CheckBox" & i)
     
     With ActiveSheet
      [B][COLOR=red]If Range(Cells(i, 3)).Value > 0 Then[/COLOR][/B]
              obCB.Object.Enabled = True
              Else:
              obCB.Object.Enabled = False
      End If
      End With
  Next i

Warship · Jun 29, 2017

Range(Cells(i, 3)) is improper syntax
Use: Cells(i, 3)
Or: Range("C" & i)

(beware of hidden cameras... bwaahaha...)

Big Lar · Jun 30, 2017

Ah-Ha!
The old improper syntax bites me again. If I could ever get my brain straight about VBA vs. Macro syntax, I'd likely never have to visit this forum again.
You've been super helpful Mr. Warship...I'm forever in your debt.

Printing Ranges when CheckBoxes = False

Big Lar

Well-known Member

Excel Facts

Warship

Well-known Member

Big Lar

Well-known Member

Warship

Well-known Member

Big Lar

Well-known Member

Big Lar

Well-known Member

Warship

Well-known Member

Big Lar

Well-known Member

Warship

Well-known Member

Big Lar

Well-known Member

Similar threads

Forum statistics

Share this page

Printing Ranges when CheckBoxes = False

Well-known Member

Excel Facts

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock