CODEWITHSUNDEEP
excelvba
Ranjan Sapkota
Ranjan Sapkota

Reputation: 41

When check box is checked, I want print preview of spec specific sheet

I had 4 checkbox A,B,C,D and i had code written such that if check box is checked show the print preview of specific sheet. I have following code:

If Sheets("setup").OLEObjects("A").Object.Value = True And Sheets("Setup").OLEObjects("B").Object.Value = True And Sheets("Setup").OLEObjects("C").Object.Value = True And Sheets("setup").OLEObjects("D").Object.Value = True Then
With Application
.ExecuteExcel4Macro "Show.toolbar(""Ribbon"",True)"
ThisWorkbook.Worksheets(Array("Production Report", "First Article", "Setup Sheet", "Spec sheet")).PrintPreview
End With

ElseIf Sheets("setup").OLEObjects("A").Object.Value = True And Sheets("Setup").OLEObjects("B").Object.Value = True And Sheets("Setup").OLEObjects("C").Object.Value = True Then
With Application
.ExecuteExcel4Macro "Show.toolbar(""Ribbon"",True)"
ThisWorkbook.Worksheets(Array("Production Report", "First Article", "Setup Sheet")).PrintPreview
End With

ElseIf Sheets("setup").OLEObjects("A").Object.Value = True And Sheets("Setup").OLEObjects("B").Object.Value = True And Sheets("Setup").OLEObjects("D").Object.Value = True Then
With Application
.ExecuteExcel4Macro "Show.toolbar(""Ribbon"",True)"
ThisWorkbook.Worksheets(Array("Production Report", "First Article", "Spec Sheet")).PrintPreview
End With

and so on for all Combination, my ribbon is initially hidden so i have this,

 .ExecuteExcel4Macro "Show.toolbar(""Ribbon"",True)"

Everywhere

If A is checked show the printpreview of production report.If A and B is checked show the printperview of production report and firstarticle.

Where A IS Production Report B is first Article c is Setupsheet and D is Spec sheet.

But the problem i am having is Production Report has 2 pages(Page 1 and page 2) on it and when i select A both pages comes as print preview.

Now i Want to create Check Box E and split Page 1 of production report to check box A and Page 2 of production report to Checkbox E. Can Someone please help me with this.

Upvotes: 0

Views: 68

Answers (1)

karma
karma

Reputation: 2009

If I understand you correctly and if you don't mind to set a print area on sheet "Production Report" then maybe you want to have a look the code below and modify it according to your need.

Sub test()
dim arr:dim txt:dim nm as string:dim i as integer
arr = Array("Production Report", "First Article", "Setup Sheet", "Spec sheet")
With Sheets("setup")
    For i = Columns("A").Column To Columns("D").Column
        nm = Split(Cells(1, i).Address, "$")(1)
        If .OLEObjects(nm).Object.Value = True Then txt = txt & arr(i - 1) & ","
    Next
    If .OLEObjects("A").Object.Value = True Then Sheets("Production Report").PageSetup.PrintArea = Range("A1:C12").Address
    If .OLEObjects("E").Object.Value = True Then Sheets("Production Report").PageSetup.PrintArea = Range("E1:F12").Address
End With

txt = Left(txt, Len(txt) - 1): txt = Split(txt, ",")
ThisWorkbook.Worksheets(txt).PrintPreview

End Sub

It create an array for each sheet name to be print-previewed as arr variable.

Then it loops to each CheckBox in sheet "setup".
On each looped checkbox value with true value, it populate txt variable with the sheet name separated by comma.

After the loop process finish, it check if checkbox "A" value is true then it set range of print area of sheet "Production Report" to A1:C12 (change according to your "Production Report" first page of the print area) ... then check if checkbox "E" value is true then it set range of print area of sheet "Production Report" to E1:F12 (change according to your "Production Report" second page of the print area) ...

then it make the txt variable into array, and finally show the print preview.

Please note:

  1. checkbox A and E must be checked to preview page2
  2. To preview just page1, checkbox E must not be checked, it must be just checkbox A which is checked
  3. it won't preview "Production Report" at all if only checkbox E is checked.

Upvotes: 1

Related Questions