Prev: Can use VSTO to reduce the size of a file?
Next: VBA Code to select and format range
From: Saga on 7 Jun 2010 14:43
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



From: Don Guillett on 7 Jun 2010 17:00
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(a)gmail.com
"Saga" <antiSpam(a)nowhere.com> wrote in message
news:hujekr$4md$1(a)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
>
>
>

 | 
Pages: 1
Prev: Can use VSTO to reduce the size of a file?
Next: VBA Code to select and format range