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
dguillett@gmail.com
"Saga" <antiSpam@nowhere.com> wrote in message 
news:hujekr$4md$1@speranza.aioe.org...
> 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
>
>
>