Save data before printing

S

Saga

Hello all,

An acquiantence is programming in Excel 2003 and has a sheet
where theuser enters receipt data. When he is done, he clicks a
boton to copythe data from the sheet to another in tabular form
withinthe same workbook. What he needs to do is to implement
a lock so that the user can't print out the receipt until after he has
clicked the boton.

His button click event code is the following:

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
'Copy data to BD2 sheet
Sheets("BD2").Unprotect
With Sheets("BD2").Range("A1000").End(xlUp)

..Offset(1, 0) = Sheets("lid Gris").Range("I17") 'Receipt number
..Offset(1, 4) = Sheets("lid Gris").Range("G12") 'Valid after
..Offset(1, 5) = Sheets("lid Gris").Range("I12") 'Valid until
..Offset(1, 6) = Sheets("lid Gris").Range("C59") 'Security code
..Offset(1, 9) = Sheets("lid Gris").Range("C19") 'Name
..Offset(1, 10) = Sheets("lid Gris").Range("C20") 'Address
..Offset(1, 11) = Sheets("lid Gris").Range("C21") 'Address2
..Offset(1, 12) = Sheets("lid Gris").Range("C22") 'City
..Offset(1, 13) = Sheets("lid Gris").Range("F21") 'ZIP
..Offset(1, 14) = Sheets("lid Gris").Range("F22") 'Telephone
..Offset(1, 15) = Sheets("lid Gris").Range("C28") 'Make
..Offset(1, 16) = Sheets("lid Gris").Range("E28") 'Model
..Offset(1, 17) = Sheets("lid Gris").Range("I28") 'Plates
..Offset(1, 18) = Sheets("lid Gris").Range("C31") 'Serial Number
..Offset(1, 19) = Sheets("lid Gris").Range("E31") 'Motor
..Offset(1, 20) = Sheets("lid Gris").Range("I52") 'Price

End With
Sheets("BD2").Unprotect

'Confirm operation
MsgBox "Saved", vbOKOnly, "Data entry"

Sheets("lid Gris").Unprotect
Application.ScreenUpdating = True

End Sub

What can you recommend? Is the functionality that he needs
possible? any orientation is welcomed, suggested reading, etc.
Thanks, Saga
 
D

Don Guillett

Look in the ThisWorkbook module for the BeforePrint event and place your
code there. Modify to suit

Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet
If .Name = "Sheet3" Then
..Range("f1").Value = 2
End If
End With
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA-User says active cell jumps to a different sheet upon Save. Please review my code 1
fill several sheets with data from other workbooks matching criter 0
VBA Macro - Copying specific data to different sheets in a workbookand linking the InputBox to a com 7
rngFound not finding correct value when more than one match 0
Code does not seem to work, but it should. 2
Update existing data or Insert new problem 3
Preserving cell validation on worksheets post data merging 1
Need assistance to populate sheet from Userform data 2

Top