TreeSaidToTheLumberjack
New Member
- Joined
- Apr 2, 2018
- Messages
- 1
Hi - I am very new to the world of VBA and am hoping to get help with the scenario below. I have a workbook with over 6 sheets, 4 of which can be printed by the user. One of sheets is a "form" sheet, one is a "controls" sheet. I want to make it so the user can select which of the pages to print by clicking on a picture on the "form" sheet (similar to a checkbox). Each picture has a macro that updates a table on the "controls" sheet with a 1 or 0. The table on the "controls" page is 3 columns by 5 rows:
I illustrated the table below. In this illustration let’s assume that this table starts on "controls" A1 and the red text is in C5
<tbody>
</tbody>
I want to use the red text in this example in the VBA Macro below to printout. I have tested the VBA below and it works if the text in blue text is typed in. I want to replace the blue text below with the dynamic string in red text above. This is where I am stumped.
ThisWorkbook.Sheets(Array("Sheet1", "Sheet2")).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\tempo.pdf", Quality:= xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=True
Thanks for reading!
- The first column is the titles of the 4 sheet names that can be printed; each on their own row
- The second column is updated by a macro on the form sheet. Each cell is updated with a 1 to inlcude the page in print job and 0 to leave it out of print job
- The third column is a formula the builds a text string with if then statements. (If column (2) is 1 then "text in column 1", if not "")
- The 5th row concatenates the text in column 3 in this format -- "Sheetname1", "Sheetname2", "Sheetname3", "Sheetname4"
I illustrated the table below. In this illustration let’s assume that this table starts on "controls" A1 and the red text is in C5
Sheetname1 | 1 | Sheetname1 |
Sheetname2 | 0 | |
Sheetname3 | 0 | |
Sheetname4 | 1 | Sheetname4 |
String Name | "Sheetname1", "Sheetname4" |
<tbody>
</tbody>
I want to use the red text in this example in the VBA Macro below to printout. I have tested the VBA below and it works if the text in blue text is typed in. I want to replace the blue text below with the dynamic string in red text above. This is where I am stumped.
ThisWorkbook.Sheets(Array("Sheet1", "Sheet2")).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\tempo.pdf", Quality:= xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=True
Thanks for reading!