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

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Saga" wrote in message
...
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