I have already posted about this problem but unfortunately no one replied! I have got a bit further with it but again I am
stuck, so thought I would plead for help again!!
I have a workbook that is made up of 5 worksheets. Each worksheet
is 8 pages long. The worksheets request various info and incorporate check boxes in some of the columns.
book will be printed at the end of each month, but not all of the pages on each worksheet will have been used. Sometimes,
perhaps only the first five lines of page one will have been completed.
The spreadsheet is going to be used by a
number of people and I want to use code that sets the print area when the user prints. I want it to print the whole page if
the first row has been completed.
Cell B8 is the first cell that the user will have to input in - this is the date
column. Therefore, if they input anything in B8 I want the page to print. I have used code to add a customer menu and have
managed to write the code which prints the whole row is cell B8, B9, B10 (and so on) is completed, but this means that when
printed it could end up with just 3 lines - I want the whole page.
I have pasted the code that I have used so far
below...I would REALLY appreciate it if anyone could spare the time to help.........(ps - the code i have used has been copie
from other help sites! i am a novice!)
Public Sub AddCustomMenu()
Dim cbWSMenuBar As CommandBar
Dim muCustom As CommandBarControl
Dim iHelpIndex As Integer
Set cbWSMenuBar = CommandBars("Worksheet Menu Bar")
iHelpIndex = cbWSMenuBar.Controls("Help").Index
Set muCustom = cbWSMenuBar.Controls.Add(Type:=msoControlPopup, Before:=iHelpIndex, Temporary:=True)
.Caption = "&Custom"
.Caption = "&Print Data List"
.OnAction = "PrintDataList"
Private Sub PrintDataList()
Dim strPrintAd As String, lRow As Long
lRow = .Range("b65536").End(xlUp).Row
strPrintAd = .Range(Cells(3, 1), Cells(lRow, 20)).Address
.PageSetup.PrintArea = strPrintAd
If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines