Save data before printing

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



0
Saga
6/7/2010 6:43:44 PM
excel.programming 6508 articles. 2 followers. Follow

1 Replies
2405 Views

Similar Articles

[PageSpeed] 38

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
>
>
> 

0
Don
6/7/2010 9:00:08 PM
Reply:

Similar Artilces: