VBA Print code printing the wrong worksheet

[ryeire]

Hi all,

I have a vba file for logging information and printing a worksheet with the logged info. However, there is a problem with the printing: the code prints the previously logged info instead of the info I've just logged (e.g. it prints the second last set of information instead of the last one) or the printed worksheet is blank. The code does work as it is intended to sometimes, but most of the time the worksheet that prints is blank or it has the wrong info.

I'm hoping someone can help me resolve this problem.

I have attached images of the information worksheet, the template worksheet, the form for logging information, the form for printing and the code for the form for printing. The code is in two images as it would not fit into one.

 

[Grasor]

Can you repost all of your code from the VBA editor into a post and surround with "CODE" code here "/CODE" tags? Where the double quotes are square brackets [ ] ??

 

[Joe4]

Here is a link that shows you how to post your VBA code that it can easily be read and copied:

How to Post Your VBA Code

Post your VBA code.

www.mrexcel.com

 

[Grasor]

You may have an issue with the way you set up your .Printout method. You are ignoring Print Areas and setting the First and Last pages to start and end at page 1.

If your data is on Page 2, it may be getting ignored entirely depending on how you've set up your Page Layout.

Try defining a Print Area. If you want to limit print out to a single page then define the print area to be one page as well and set your Max Width and Max Height values on the Page Layout Ribbon to "1" each so you can view the page limits in a different spreadsheet view.

FYI: Every parameter in the .PrintOut method is optional.

Sheets.PrintOut method (Excel)

Office VBA reference topic

docs.microsoft.com

 

[ryeire]

This is the code for the print form

Dim PR As Variant
Dim C As Variant

Private Sub cmdCancel_Click()

Unload Me


End Sub

Private Sub cmdContinue_Click()

If txtPrinter.Value = "" Then
Call sbMsgBlank

Else


'Sheets.PrintOut(From,To,Copies,Preview,ActivePrinter,PrintToFile,Collate,PrToFileName,IgnorePrintAreas)

PR = txtPrinter.Value
C = txtCopies.Value

Worksheets("Print Layout").PrintOut From:=1, To:=2, Copies:=C, Preview:=False, ActivePrinter:=PR, PrintToFile:=False, Collate:=True, IgnorePrintAreas:=True


End If


End Sub

Private Sub ToggleButton1_Click()

End Sub

Private Sub spnCopies_SpinDown()

If txtCopies.Value > 1 Then
txtCopies.Value = txtCopies.Value - 1
ElseIf txtCopies.Value = 1 Then
txtCopies.Value = txtCopies.Value - 0

End If


End Sub

Public Sub spnCopies_SpinUp()


If txtCopies.Value < 10 Then
txtCopies.Value = txtCopies.Value + 1
ElseIf txtCopies.Value > 10 Then
txtCopies.Value = txtCopies.Value + 0

End If



End Sub

Public Sub sbMsgBlank()

MsgBox "Enter a valid Printer Name", vbExclamation, "Printer name missing"



End Sub

Private Sub UserForm_Click()

End Sub

 

[ryeire]

This is the code for the data logging form

Option Explicit
Dim TCO As String
Dim Originator As String
Dim InDate As String
Dim EffectFrom As String
Dim EffectTo As String
Dim Status As String
Dim Part As String
Dim WO As String
Dim LotNum As String
Dim Details As String
Dim Reason As String
Dim i As Integer
Dim Check As Boolean
Dim DayNow As String
Dim ExpDate As String
Dim t As Integer
Dim r As Integer
Dim Date2 As String


Private Sub cmdClear_Click()
txtTCO.Value = ""
txtOriginator.Value = ""
txtInDate.Value = Date
txtEffectFrom.Value = ""
txtEffectTo.Value = ""
txtStatus.Value = "OPEN"
txtPart.Value = ""
txtWO.Value = ""
txtLotNum.Value = ""
txtDetails.Value = ""
txtReason.Value = ""

End Sub

Private Sub cmdExit_Click()

Unload Me

End Sub

Private Sub cmdLog_Click()


If txtOriginator.Value = "" Or txtPart = "" Or txtWO = "" Or txtLotNum = "" Or txtDetails = "" Or txtReason = "" Or txtTCO = "" Then
Call sbMsgBoxWarning

Else

'Assign Variables to Input Fields
TCO = txtTCO
Originator = txtOriginator
InDate = txtInDate
EffectFrom = txtEffectFrom
EffectTo = txtEffectTo
Status = txtStatus
Part = txtPart
WO = txtWO
LotNum = txtLotNum
Details = txtDetails
Reason = txtReason

'Set Conditions for "Check" variable
ActiveCell.Select
If ActiveCell = "" Then
Check = True
ElseIf ActiveCell <> "" Then
Check = False
End If

If Check = True Then
i = ActiveCell.Row
ElseIf Check = False Then
i = i + 1
End If


'write data to sheet
ActiveWorkbook.Worksheets("TCO Log").Cells(i, 1).Value = TCO
ActiveWorkbook.Worksheets("TCO Log").Cells(i, 2).Value = Originator
ActiveWorkbook.Worksheets("TCO Log").Cells(i, 3).Value = InDate
ActiveWorkbook.Worksheets("TCO Log").Cells(i, 4).Value = EffectFrom
ActiveWorkbook.Worksheets("TCO Log").Cells(i, 5).Value = EffectTo
ActiveWorkbook.Worksheets("TCO Log").Cells(i, 6).Value = Status
ActiveWorkbook.Worksheets("TCO Log").Cells(i, 7).Value = Part
ActiveWorkbook.Worksheets("TCO Log").Cells(i, 8).Value = WO
ActiveWorkbook.Worksheets("TCO Log").Cells(i, 9).Value = LotNum
ActiveWorkbook.Worksheets("TCO Log").Cells(i, 10).Value = Details
ActiveWorkbook.Worksheets("TCO Log").Cells(i, 11).Value = Reason


'clear fields after entry
txtTCO.Value = ""
txtOriginator.Value = ""
txtInDate.Value = Date
txtEffectFrom.Value = ""
txtEffectTo.Value = ""
txtStatus.Value = "OPEN"
txtPart.Value = ""
txtWO.Value = ""
txtLotNum.Value = ""
txtDetails.Value = ""
txtReason.Value = ""

ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate

End If



End Sub

Private Sub cmdPrint_Click()

frmPrint.Show

ActiveWorkbook.Worksheets("Print Layout").Cells(3, 7).Value = Date
ActiveWorkbook.Worksheets("Print Layout").Cells(7, 7).Value = txtInDate.Value
ActiveWorkbook.Worksheets("Print Layout").Cells(7, 3).Value = txtTCO.Value
ActiveWorkbook.Worksheets("Print Layout").Cells(10, 2).Value = txtEffectFrom.Value
ActiveWorkbook.Worksheets("Print Layout").Cells(10, 6).Value = txtEffectTo.Value
ActiveWorkbook.Worksheets("Print Layout").Cells(12, 3).Value = txtOriginator.Value
ActiveWorkbook.Worksheets("Print Layout").Cells(14, 3).Value = txtPart.Value
ActiveWorkbook.Worksheets("Print Layout").Cells(15, 3).Value = txtWO.Value
ActiveWorkbook.Worksheets("Print Layout").Cells(16, 3).Value = txtLotNum.Value
ActiveWorkbook.Worksheets("Print Layout").Cells(20, 2).Value = txtDetails.Value
ActiveWorkbook.Worksheets("Print Layout").Cells(26, 2).Value = txtReason.Value
ActiveWorkbook.Worksheets("Print Layout").Cells(15, 7).Value = txtStatus.Value
ActiveWorkbook.Worksheets("Print Layout").Cells(42, 8).Value = txtTCO.Value







End Sub

Private Sub cmdToday_Click()


txtInDate.Value = DayNow
txtEffectFrom.Value = DayNow
txtEffectTo.Value = DateAdd("m", 6, DayNow)





End Sub

Private Sub cmdNextR_Click()

Dim n As Integer

ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
n = ActiveCell.Row

txtTCO.Value = ActiveWorkbook.Worksheets("TCO Log").Cells(n, 1).Value
txtOriginator.Value = ActiveWorkbook.Worksheets("TCO Log").Cells(n, 2).Value
txtInDate.Value = ActiveWorkbook.Worksheets("TCO Log").Cells(n, 3).Value
txtEffectFrom.Value = ActiveWorkbook.Worksheets("TCO Log").Cells(n, 4).Value
txtEffectTo.Value = ActiveWorkbook.Worksheets("TCO Log").Cells(n, 5).Value
txtStatus.Value = ActiveWorkbook.Worksheets("TCO Log").Cells(n, 6).Value
txtPart.Value = ActiveWorkbook.Worksheets("TCO Log").Cells(n, 7).Value
txtWO.Value = ActiveWorkbook.Worksheets("TCO Log").Cells(n, 8).Value
txtLotNum.Value = ActiveWorkbook.Worksheets("TCO Log").Cells(n, 9).Value
txtDetails.Value = ActiveWorkbook.Worksheets("TCO Log").Cells(n, 10).Value
txtReason.Value = ActiveWorkbook.Worksheets("TCO Log").Cells(n, 11).Value





End Sub

Private Sub cmdPrevR_Click()

Dim p As Integer

ActiveCell.Offset(rowOffset:=-1, columnOffset:=0).Activate
p = ActiveCell.Row

txtTCO.Value = ActiveWorkbook.Worksheets("TCO Log").Cells(p, 1).Value
txtOriginator.Value = ActiveWorkbook.Worksheets("TCO Log").Cells(p, 2).Value
txtInDate.Value = ActiveWorkbook.Worksheets("TCO Log").Cells(p, 3).Value
txtEffectFrom.Value = ActiveWorkbook.Worksheets("TCO Log").Cells(p, 4).Value
txtEffectTo.Value = ActiveWorkbook.Worksheets("TCO Log").Cells(p, 5).Value
txtStatus.Value = ActiveWorkbook.Worksheets("TCO Log").Cells(p, 6).Value
txtPart.Value = ActiveWorkbook.Worksheets("TCO Log").Cells(p, 7).Value
txtWO.Value = ActiveWorkbook.Worksheets("TCO Log").Cells(p, 8).Value
txtLotNum.Value = ActiveWorkbook.Worksheets("TCO Log").Cells(p, 9).Value
txtDetails.Value = ActiveWorkbook.Worksheets("TCO Log").Cells(p, 10).Value
txtReason.Value = ActiveWorkbook.Worksheets("TCO Log").Cells(p, 11).Value




End Sub

Private Sub UserForm_Initialize()

r = Worksheets("TCO Log").UsedRange.Rows.Count
r = r + 1
ActiveWorkbook.Worksheets("TCO Log").Cells(r, 1).Activate




txtStatus.Value = "OPEN"
DayNow = Date
DayNow = Format(Date, "dd/mm/yyyy")


txtInDate.Value = DayNow
txtEffectFrom.Value = DayNow
Date2 = Format(Date, "dd/mm/yyyy")
Date2 = DateAdd("m", 6, DayNow)
txtEffectTo.Value = Date2




ActiveCell.Offset(rowOffset:=-1, columnOffset:=0).Activate
t = ActiveCell.Value

txtTCO.Value = t + 1

txtOriginator = Environ$("Username")

ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate

End Sub

Public Sub sbMsgBoxWarning()

MsgBox "One of the fields has been left blank", vbQuestion, "Fields Incomplete"




End Sub

 

[ryeire]

@Grasor, Thank you for your insight I will try that

 

[ryeire]

@Grasor I have tried all your suggestions but unfortunately none of them worked. It has actually nearly made things worse and now no info is being printed on the worksheets at all.

 

[RoryA]

Unless your printform is modeless, the code stops after showing the form and before actually populating the print sheet with the relevant data. The frmPrint.Show line should be at the end.

 

[ryeire]

@RoryA that has fixed my problem. Thank you very much for your help