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

Similar Articles

[PageSpeed] 25

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:

SQL in Excel data
Hi all, Is there a possibility/way to run an SQL query in an excel data sheet? I have quite some data like the sample below, now i would like to have the sum of spending for each person. Like it is possible in Access. A1 B1 Field1 Field2 Chuck 12,89 Mike 23,09 Jean 9,34 Chuck 30,00 Mike 3,80 Chuck 22,00 Mike 7,23 Jean 10,55 Jean 10,75 Jean 31,45 Chuck 19,99 Result Field1 SumOfField2 Chuck 84,88 Jean 62,09 Mike 34,12 Advice would be appriciated. Cheers, Ludovic Hi You could use a formula like this ...

Printing Newsletter in Publisher 2003
I have Publisher 2003 on an XP OS and trying to print a newsletter with 2 pages per tabloid size paper. I was able to do this OK with Publisher 2000. It seems that the 2003 version does not have a "Book fold" option for the layout. The Print Preview looks OK and shows page 6 and 1 for the layout. For layout I choose "Booklet". What I get is page 6 and 1 on 1/2 of the 11x17 paper. Help! Gary Gary are you printing to a laser printer??? If you are, you are running out of printer memory. Have a look in the properties of the printer driver, and change the settings from Vector...

How to get XML data out of an XML file
I am trying to retrieve the Parameters first or second (0, 1 ,2) node from the following XML file: <?xml version="1.0" encoding="utf-8" ?> <Robot xmlns="http://tempuri.org/RobotDefaults.xsd"> <Parameters> <Name>Decker</Name> </Parameters> <Parameters> <Name>A</Name> </Parameters> <Parameters> <Name>B</Name> </Parameters> </Robot> There will be more data than just a name for each Parameters node. Here is my code: Dim node As XmlNode = xmlDocument.SelectSingleNode("/P...

excel, worksheet, set print area, position selection on printed pa
Office 07, Excel. Print area is set, print preview displays selection, always on far left of page. I want to choose the position on the page, eg centered or right aligned etc. Help please. Hi, To get your printout to center on the page chose Page Layout, click the Page Setup quick launch button (bottom right corner of the Page Setup group). Choose the Margins tab, click Horizontal. There is no command to right align a printout, but you can change the left margin to force the printout to the left. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Eve Al...

Can't edit print styles in Outlook 2007
Weekly calendar view to print - Define print Styles - Edit "CANNOT DISPLAY THE DIALOG BOX" I'm using trial version - shouldn't be the case???? thks I'm having the same problem where I cant edit the print syles ... please let me know what ou find out. I think being able adjust the format and print the calendar is important, as I usually print my calendar. EggHeadCafe.com - .NET Developer Portal of Choice http://www.eggheadcafe.com Nate This is an Excel newsgroup. I would suggest posting this to an Outlook one -- HTH Nick Hodge Microsoft MVP - Excel Southampton...

Error saving a Shared Workbook
I have a user using a shared workbook and gets an error everytime she does this ONE specific change. She deletes a line and then saves and gets a message that says, "EXCEL.exe has generated errors and will be shutdown by Windows... blah blah blah". If she makes anyother type of changes it saves just fine. I also had another user do the SAME change and that person also got that error message. When the file is changed to "Not Shared" then the line is deleted it saves fine also. The problem is obviously with the file itself and that is shared out. can anybody ...

Locking cell color while allowing data changes in cell
In excel 2000, I created an attendance worksheet for my classes.(Alphabetized names down left vertical column. Dates across top of horizontal row.) I added a different color to all cells in every other row to make for easier reading of each student's name and absences. Every other row stays with a white background. My question: I wondered if it was possible to lock row colors while allowing data to change on top of them. If a new student is added to my class in alphabetical order, the alternating color pattern is often lost. It is a pain to rechange row and cell colors. Any shortcut ...

How refresh imported data automatically?
In Excel, I've imported data into a worksheet. I know I can click Data.. Refresh Data to requery the source, but I want to be able to do it in a more automated way. Is there some sort of macro I can write when opened or something? Thanks, Ron Ron, Select the cell the data starts in, select Data/Import External Data/Data Range Properties Check the box "Refresh on file open" and anything else that needs checking off... Other than that, you could record a new Macro that selects your cell, refreshes data, and assign the macro to a keystroke, or even an icon. Beege ...

Question About Missing Data
So, this is probably really easy, but I just want to ask and see if I may be missing something here. Some data on vendor numbers changed. Let’s say IBM used to have a vendor number 12345 and now it’s vendor number is 56789. I can identify IBM as IBM, but I really want to use the number, not the name. Should I set up a table that ties the numbers together, so that Access knows 12345 = 56789? Or, should I do some kind of Update Query and change all incidences of 12345 to be 56789? Or, is there some other, method, like a ‘best practices for missing data’? Thanks! Ryan--- ...

Outlook Data Files #4
I've done some reading but I'm still confused about the use of Office Outlook Personal Folders File (.pst) versus Outlook 97-2002 Personal Folders File (.pst). I understand the basic "Office Outlook Personal Folders File (.pst) to create a new Outlook 2003 .pst file. Click Outlook 97-2002 Personal Folders File (.pst) to create a new Outlook .pst file that is compatible with earlier versions of Outlook." When we have upgraded to Outlook 2003 or set up a new PC with Outlook 2003, when adding PST's you can chosse either of the two file type options and browse to chos...

New Named Range Created Each Time Data Imported into Excel via Macro
I have noticed that each time I import data into an Excel spreadsheet via a macro, a new named range (for the same range) is created. This does not pose a problem, but after a while, I'll have a huge number of named ranges that will never be used. Why does Excel name the range and how can I stop this? I noticed in the recorded macro, there was a line .Name = "drd_5". I commented this out to see what would happen, but it just renamed the range "ExternalData_5". The next one was "ExternalData_6", etc. Thanks, Carroll Rinehart You could refresh with differen...

Find what control is using a data item
I built a form, then deleted 2 columns from the source table and now I get a popup asking for the value of those 2 columns. The problem is, I don't use those columns so I need to find what on the form is refferencing the deleted columns. I have looked at the control drop down on the properties window and the tab order window and can't find a control with either one of the missing column names. How do I determine what is trying to refference the deleted columns? I found the problem, the column was still referenced in the underlying query that fed the form. "MeSteve"...

merging data
Hi all:) I'm new to this forum and to to Excel (2000) and not sure that I'm asking this question correctly but here is what I'm trying to do. The data on my work sheet is pasted from another sorce and includes information: name, ref #, time, date. I use this information looking up the status of an order and enter a "status note" in 5th (last) column. As the day progresses, the source that the data comes from is copied and pasted under the current list I already have going and much of the data on the lower list is a duplicate. How can I merge the to list so that I hav...

auto fill in data when changing fields
i new to crm 4 and i would like to; once i selected the account i need it to grab the main phone and put it in the phone field, and could you tell me where i need to do this, thanks -- j.hardy you can modify the mappings of the corresponding relationship "moon" wrote: > i new to crm 4 and i would like to; once i selected the account i need it to > grab the main phone and put it in the phone field, and could you tell me > where i need to do this, thanks > -- > j.hardy ...

Check printing in GP 7.0
Dear Guys, I have check payments for vendors, where I'm dealing with LOCAL CURRENCY & FORIGHN CURRENCY. When I paid by check in LOCAL CURRENCY then the amount in words prints perfectly but if i paid by check in FORIGN CURRENCY then the amount in word prints in numeric which i want to print amount in words instead of numbers. Please help me to print checks amount in words for FORIGN CURRENCY as well. Your suggestion would be appricated on above issue thnx Shamin ...

Events template not saving entries
I am attempting to build an events database using the Events template in Access 2007. I have made no modifications to the template. It is not saving the information I add into the Event List table. I first tried entering information directly into the table. Then I tried entering information using the New Event in the Link bar. I can find no way to get the entries to save. Has anyone else had this problem? Is there a fix somewhere? Does anyone have anything to suggest? Thanks. -- M'Lou *:) On Sun, 7 Mar 2010 05:05:01 -0800, M''Lou Wilson <MLouWilson@discussions....

Outlook Draft Email Print Problem
I have emails saved in draft but when i go to print them it doesnt show who it is being sent to and the fact it hasnt been sent yet, i have played with the setting but to no avail. I am using outlook 2003 any help would be appriciated. Turn off WordMail before printing the draft (Tools | Options | Mail Format) or wait to print until after you send it. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx <kpmail76@yahoo.co.uk> wrote in message news:...

Pivot Table data values
I need make a pivot table using the values as the data. I used to be able to do this, but now it only sums, counts or calculates. Is there a way to return the data values? ...

How can I use a form (Excel) to add a column of data to the right.
How can I use a form (Excel) to add a column of data to the right of a list? It's a simple matter to add a column to the left of a list by writing a macro that inserts a column and then pastes the values. However I want to have the data pasted on the right of a list, if only so that I can easily run a chart from it. ...

Can I insert Google Calender data into Publisher document?
How can I configure my Publisher document to use the data from Google Calendar? I would like to use the RSS feed from Google Calendar. Creating an RSS Feed http://blogs.msdn.com/lisawoll/archive/2004/11/19/266972.aspx -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "thpope" <thpope@discussions.microsoft.com> wrote in message news:CB5CDF70-1B8E-45E1-B17B-7D496CD96F36@microsoft.com... > How can I configure my Publisher document to use the data from Google > Calendar? I would like to use the RSS feed from Google C...

Business cards & printing
I have formatted some business cards with Publisher 2003 for a friend and she wants to be able to print them off from her computer. However she does not have MS Publisher, MS Word. She will probably purchase one of the Avery packages from an office supply. Is there a way I can save the file ready to print the cards with the proper number per page and convert it to jpg so she could use it? Thanks for information. Susan Hi Susan (dsnsacree@msn.com), in the newsgroups you posted: || I have formatted some business cards with Publisher 2003 for a || friend and she wants to be able to print ...

Printing Detailed Trail Balance
I just installed a new printer HP Deskjet 5940 and when i print Detailed Trial Balance reports it cutting off the Credit column. How do I fix this? Thanks! ...

How can I Show data as a % of Sub Group in a Pivot Table
I want to show a column of data (% of City) as a % of a sub group total. Here's my example of how the pivot table should look: State City Carrier Sq Yards % of City (Sq Yards2) Alabama Birmingham Carrier 1 10 20% Carrier 2 20 40% Carrier 3 10 20% Carrier 4 10 20% Birmingham Total 50 100% Mobile Carrier 1 10 25% Carrier 2 10 25% Carrier 3 1...

Pivot Tables filtering data
Using Excel, I have query(using an ODBC connection) of all customer order detail over a two year time period. The detail for each customer is different in that some customer have multiple orders, and the amount of individual sales parts they order may be 1 or 100. My query gathers detail by order line item. We were able to provide management with comparative sales data. Now that we have shown this report to management, they want three things that I'm unsure how to provide. 1) Customers that are new in the current year2) customers that had orders in both years, 3)customers that ...

How do I plot data on a map in excel
I am using Microsoft office standard 2003. I wish to plot information on a local area map within the Clapham Park area, SW2. Hi, There is no built-in functionality for this. You would need a dedicated Mapping program with UK based information. You can however simulate the chart. If you have an image of the map you could use that as the plotarea pattern. You could then use a xy-scatter graph to plot information over the map. You would have to determine the coordinates by trial and error. Cheers Andy Byron wrote: > I am using Microsoft office standard 2003. I wish to plot informat...