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

Similar Articles

[PageSpeed] 39

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:

FileNotFound returned by Bitmap::Save() when saving to stream
I have a little chunk of code which is supposed to take a bitmap image and save its PNG encoding into a block of memory. The problem is, I keep getting a FileNotFound error back from Save(). If anyone has any ideas, I'd love to hear them. Here's the code (more or less) in question; there really shouldn't be much to it, I would have thought: /* Allocate an IStream object to write the data into a global memory block. We indicate that the handle to the underlying data will be freed when the stream object is released. */ VERIFY(S_OK == ::CreateStreamOnHGlobal(nil, True, static_c...

How do I print query parameters on the report?
I'll try to keep this short. I have created a query with multiple parameters. I want to print reports showing invoices to a specified customer between certain dates. When I run the query, it is set up to enter the customer name first, and then type the beginning date of the invoice period, and then type the ending date of the invoice period. I have that part down okay. When I print the report, I want the date parameters that I typed in for the query to appear on the printed report. I created a text box in Design View on the report, and for the Control Source, I entered the sam...

Handling of savings accounts and 401k accounts in the budget
I noticed that Money treats money that I contribute to a 401k account or transfer to a savings account as an expense. Basically, the more I contribute, the worse my budget looks. Is there a way to prevent this from happening? Right now, I exclude transfers to my savings account from the budget and it solves the problem, however, I shouldn't have to manually do this each time. For 401k contributions, I cannot find a way to do this. Why does it show up as "an expense" (what Category???) instead of something like Transfer Out of Budget Accounts? The 401k cash account sh...

Modify scale in X axis on a scatter plot from Pivot data
I need to increase the gap between my labels on the X axis in a scatter plot. They are too close and I can not read the labels unless i make them 4pt and zoom in to 200%. There are about 200 points on the X axis at .2 increments. I would like to be able to have labels for whole numbers only. I can not use a fixed set of values by generating a dummy X-axis since the range changes as I apply the different filters. Any ideas? thanks! Sandy Pivot charts don't let you change the spacing of the categories. They also don't let you make an XY (scatter) chart, so you probably have ...

printing customer statements.
Two of my RMS customers are very unhappy about the lack of options in printing customer statements. I have not been able to find a solution for either customer. Dixie Products, Inc. needs to print selected groups of customer statements. They have different types of customers and have requested the ability to select out which customer statements to print. Dixie upgraded to RMS from Quick Sell 2000. They say QS2000 had the ability to run selected statements and they would not have purchased RMS if they had known of this limitation. Another Customer needs to exclude printing statements w...

Printing a two spreadsheets front and back
I have clicked select all sheets and selected two sided copy in print options. It gives me a message that 1 page has been printed, when I hit print, but when I go to the printer, there are two sheets of paper. Each spreadsheet on its own piece of paper. i have done this before, but when I do I print several copies, and months go by before I have to print the two sheets front and back again. Help. hi technically all excel does is send the data. seems to me the problem may be in your printer, not excel. make sure that you are printing to a duplex printer. regards FSt1 ...

Unable to save password in Outlook 2003
I cannot seem to save my "network password" for Outlook 2003. I tried everything I can think of. I tried editing the registry. I use XP. I try going into Control Panel, go to mail and go thru the process there and....nothing worked there. I uninstalled Office the reinstalled it.....nothing! I even went as far as unistalling my internet software, still...nothing! Someone told me that XP will not save passwords. My Outlook express works fine. Someone HELP! I also had this issue in Outlook 2003, but also in indows Live Messenger and Internet Explorer 7. I spend hour...

what encoding does system.xml.xmldocument.save(string path) use to save the xml document if there is no <?xml... in the front of the xml document?
what encoding does system.xml.xmldocument.save(string path) use to save the xml document if there is no <?xml... in the front of the xml document? Daniel wrote: > what encoding does system.xml.xmldocument.save(string path) use to save the > xml document if there is no <?xml... in the front of the xml document? UTF-8 is the default encoding. -- Oleg Tkachenko [XML MVP, MCPD] http://blog.tkachenko.com | http://www.XmlLab.Net | http://www.XLinq.Net utf8 i suppose would be default Vinu Baby "Daniel" <softwareengineer98037@yahoo.com> wrote in message news:e8#...

Value of a data point in a graph
Hi folks, I use following algorithm to read the value of data point: >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> On Error Resume Next For Serie = 1 To NumOfRows For DataPnt = 1 To NumOfColumns Set ChtPt = _ ActiveChart.SeriesCollection(Ser...

Save and save as
Hi, I am looking for some help with a macro, I need to disable anyone from saving a file or save as and also disable the prompt if the close button is pressed you get an option to save the file then, I need to disable all of these, also have a pop up box to warn that save as been disables when a user tries to save the file fro save and save as. Can anyone help me with this. I am using word 2003 Regards Mark Once you give someone access to a document, you have ceded all control over it. You can however stop them from overwriting the original file by making the doc...

Cell data changes when copying and pasting
I've got Excel 2002 SP2. Another person in our office has Excel 2000. She entered a bunch of data into a spreadsheet for me (the data is dates in mm/dd/yyyy format). When I copy and paste her data into a new spreadsheet in one of my existing files, the dates change. This seems to be the rule of what is happening: when I copy and paste a cell into my sheet, it adds 1 to the day and 4 to the year. For example, if she typed 12/18/2003, it pastes it as 12/19/2007; if she typed 4/15/2004, it pastes as 4/16/2008. We are both baffled by this. There are no formulas being used, just a basic...

How to retrieve source data from data plots only?
Hi, I would appreciate any help on this. Basically, I have got some plots in Excel from my colleague. He had deleted all the source data used to generate those plots since he only cares about the plots. However, the source data is what I really need. I know by clicking on each data point in a plot, you can view the value of that data point. But is there a way to retrieve all the source data at once? There are simply too many data points in a plot for me to do this one by one. Thanks, Fang I have found a macro for doing this. I am posting the link here in case someone runs into the same p...

ATTN: Need to import data from access database, hosted on web.
This should be fun. I can use the import data feature to get data into excel from a loca access database, But i need a central access dbase on the web, and when someone open the excel workbook, it auto updates from the access dbase hosted on th web server. Im assuming this has something to to with the connection strings et that i need to write, could anyone point me in the right direction o post a solution, many many thanks in advance. Pau -- Message posted from http://www.ExcelForum.com ...

Saving Images
When I right click to save an image in an email, it will only save as a .bmp. I need to use it as a .jpeg or a .gif. How can I fix this? In news:AB8040CE-5975-4A14-87BD-BE3B97E33682@microsoft.com, Angi <Angi@discussions.microsoft.com> typed: > When I right click to save an image in an email, it will only save as > a .bmp. I need to use it as a .jpeg or a .gif. How can I fix this? Clear your internet explorer temp file cache.....go to control panel, internet options, and choose 'delete files' "Angi" <Angi@discussions.microsoft.com> wrote in message...

Print problems
For publishing a company catalogue I have chosen a template with a single start page, single end page and inbetween double sided pages obviously placed there for some kind of convenience for later laminating or stapling. My problem is that, instead of relying on my copyshop ready-printed ones, I want to be able to print this out at the company as well when I need a copy, but then double sided! My color laser is set to print duplex, so that part is 100% ok. Why does the document still print out as single-sided? What have I done wrong? Should I choose another template that is already se...

"Unattainable" savings goals
I've learned to mostly ignore it, but it drives me batty. I have a particular loan with a goal to pay it off by 7/4/04. The loan balance is 14,000. I have marked a savings account to be "used for budgeted savings goals." I deposit $1300 per month into the account which has a balance currently of $13000. Money 2K4 says that I cannot reach my savings goal. What can I do to help fix this? Any feedback would be appreciated! ...

Not printing hyperlinks
Is there a way to print the page without the hyperlinks showing up on the printout? I know you can with objects, but don't know about hyperlinks. Thanks!! Hi mark1 With a macro it is possible Sub test2() Dim hyp As Hyperlink For Each hyp In ActiveSheet.UsedRange.Hyperlinks hyp.Range.Font.ColorIndex = 2 Next hyp ActiveSheet.PrintOut For Each hyp In ActiveSheet.UsedRange.Hyperlinks hyp.Range.Font.ColorIndex = 5 Next hyp End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "mark1" <anonymous@discussions.microsoft.com> ...

cannot print budget need help
Hi running Vista Home premium as my OS and IE 8 and MS Money +. I can print the monthly spending by category but when I view the budget report iI xannot figure out how to print it. The help said to look for print this report in the left task pane but that option is not there. So I clicked on file and tried to print there but the print option is greyed out. The help said I could also send it via email as a. txt file. So how can I get this report printed? Any help would be greatly appreciated. Thanks Kaja ...

Saving default tabs
I can't seem to get a change in the default tab setting to save. When I reopen publisher the tab setting reverts to .5 which was original default. I'm I the only person who is having this problem? I'm using the 2003 verson but had the same problem with 2000. Why not take the default page, set the tabs you want and save it as a template with a file name of, "My Very Special Tabs Settings for Future Files.pub" (jerking your chain a bit<G>) Then when you want to create what you want, use your created template file and when your pub file is what you want, do a Sa...

Getting data from a webpage
Okay, I'm needing to do this same thing again, but can't figure out where the item/innertext I need is: The webpage is: http://www.gocomics.com/calvinandhobbes/2010/01/16 ....and what I need is the image (on this particular page it's): http://imgsrv.gocomics.com/dim/?fh=83fba46523c3b2f31aeb078a548010a8&w=900.0 thanks, SouthernAtHeart "Joel" wrote: > You don't need to get the source. You can use the IE explorer like below to > help you filter the data. The source is under the IE.Document.all property. > You can get these lines...

Save to unProtect a Document?
Our company has word documents that we use as templates on a fileserver. How could I protect the documents so that when they open them, they dont make changes to the server version, but save their OWN version and do what they want? First of all, make use of true *template* files (with the *.dot or *.dotx extension). Also, assign the template files the read-ony attribute. -- Stefan Blom Microsoft Word MVP "Chris" <Chris@discussions.microsoft.com> wrote in message news:EB9D352D-823F-4126-8A00-2D90516A3FA1@microsoft.com... > Our company has word docum...

Exchange 2007 And Daylight Savings Time Update
Is there a patch I need for Day light savings time and Exchange 2007? Thanks! No CDO patch required (no equivalent of KB 926666). However, the Windows Server 2003 OS will required KB 931836 time zone update and Calendar-items in 2007-hosted mailboxes created using old DST 2006 rules will need to be rebased. -- Bharat Suneja MVP - Exchange www.zenprise.com NEW blog location: exchangepedia.com/blog ---------------------------------------------- "D303M" <D303M@discussions.microsoft.com> wrote in message news:A3132ABD-4614-402F-9654-61EA72501112@microsoft.com... > I...

Save csv format to dbf format
I have a .csv file with 13 columns of data opened in excel. I make a few changes to a field and then want to save to .dbf format. After saving as .dbf when I open the file only column 1 data is present. Any insights into where the rest of the data goes? Why does the data disappear? Thanks, Mike -- mdelasaux ------------------------------------------------------------------------ mdelasaux's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28889 View this thread: http://www.excelforum.com/showthread.php?threadid=486365 Try using Notepad and open your .dbf...

when I save an email, where does it save to?
Hi all, I have several emails that I wanted to reply, and I did click "reply" and then wrote something, but I did not have time to finish... then I click on "save", and in the evening, I closed the outlook because I slept... now I reopen the outlook in the morning, and I want to find my saved unfinished replies that I was writing... But where to find them? I have a "draft" folder, but those saved emails do not seem to be in this folder... wherelse could they go? ---------------------------------------------- This is annoying... is there a way to automat...

How do I enlarge my excel spreadsheet so readible when printed??
after I have worked on the excel spreadsheet which has 8 columns and approx. 72 lines. Then I go in to print and the page setup is at 15, when I go to change it then it changes my page breaks. I try to print and it prints in very small print eventhough my font is at 16 - 20. It also only prints a 1/4 of the page. I am open to any suggestions as I need this chart for an important meeting. Please help!!! Thank you One possibility is that the page setup settings. In the File | Page Setup... dialog box in the Page tab ensure you have Scaling set to 'Adjust to 100% normal size'. ...