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

.pst data file
I am using outlook 2002 with Office Professional XP. When I try to delete an item from the delete folder is get an error message that states that my .pst file cannot be accessed. Access denied. I have compacted the file and ran scanpst. Neither resolved the problem. Any help would be appreciated since I no longer can receive mail either. The email address above is my work email. The outlook problem is on my home computer. Thanks Try deleting and recreating your profile. -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Having searc...

Data query drive error
After running a sql qeury in excel 2002, receive a message error with "\" character and the help info box stating "driver error". ...

Help on importing Money 1999 data into 2003.
When I try to import my data into my Money 2003, the data comes across as bad or somethings I cannot import it? Any ideas? Please identify the exact error message. If it's "incompatible versions," see http://umpmfaq.info/faqdb.php?q=1. "defpro@yahoo.com" <anonymous@discussions.microsoft.com> wrote in message news:0d3201c484cc$a2833880$a301280a@phx.gbl... > When I try to import my data into my Money 2003, the data > comes across as bad or somethings I cannot import it? > Any ideas? Also, what you want to do is **NOT** File|Import. It's File|Op...

Printing selected data
Hello, Using Excel 97, I have set up a spreadsheet that has about three pages of data. Columns A through D give item description. Columns E through J have been left blank so as to let other people enter the quantities they need. My problem is this: I need to be able to print out only rows that have quanitites entered in columns E through J. I don't want to print out the entire list of three pages. I have tried Advanced Filter, which works reasonably well, however other people with less computer skills will also be printing, so it must be made simple to use. Is there a macro, function, form...

Help with print setup
Hi I am using Excel 2000 SP3. I have a Workbook with 59 worksheets and need to print out a part of each worksheet, it is the same area on every worksheet, is there a way I can set this up without having to set Print area and Page setup on each worksheet. thanks Daniel Group all the worksheets. Right click on a worksheet tab select "select all sheets" do the page setup once. rightClick on any of the worksheet tabs and select "ungroup sheets" If you leave those sheets grouped and start working on them, then almost everything you do to one, you do to all in the gro...

Improperly Saved Changes on an Attachment
Is there any way to retrieve changes made to an attachment that were manually saved, but not "saved as" and placed in a folder on my computer? I use Vista's Windows Mail to recieve my mail. The file is probably located in a folder somewhere in C:\Users\(youraccount)\AppData\Local\Microsoft\Windows\Temporary Internet Files\ "\AppData" is normally hidden "\Temporary Internet Files" is normally a hidden protected Operating System file You'll have to remove the checkmark (if they're hidden) in Windows Explorer: Organize - Folder & Sea...

email is not delivered to outlook data file
I have mutliple email POP3 accounts. I went in and created a new .pst data file and on account settings linked email1 with data file1 and email2 with data file2 for incoming messages to be delivered. However, all emails coming in still go to the original data file1 inbox....How do I get emails delivered for email2 to the data file2 inbox? It shows in the account settings to be correct Hi Billie, Outlook only has 1 default PST file which is your "Personal Folder" with your "Outlook Today". All incoming emails will be delivered into this "Inbox&quo...

Bitmap Printing problem
I am trying to print a bitmap using CDC::StretchBlt function,The application is printing well on some systems but a blank page is getting printed on the others, where all the systems are in LAN and using the same OS(Win XP) & ServicePack 1A(version) and printer drivers(Versions Note:The same is being displayed on the window and there exists no problem. Check out the bitmap printing example in the MSDN. Printing a bitmap isn't straightforward, and in addition, not all printer drivers support bitmap printing. joe On Fri, 30 Apr 2004 08:16:07 -0700, "Bitmap Printing problem&quo...

Printing clips the side of the document words
Hello, I've just installed all the updates for Windows XP that were required and others also and have Office 2003 with Outlook 2003. I've tried to print an email message and the words in the narrative are clipped on the right and left margins. The "TO" "FROM" "SUBJECT" and all the other header information is aligned correctly and the problem is only with the main text portion. When I print regular WORD documents, I don't have this problem and my printer can print directly to the edge of the paper (Laser). The formatting is coming from Outlook a...

Linked files
I have s/s#1 which uses vlookup to locate data in a seperate file s/s#2, and returns the data to s/s#1. This is working ok. Assuming I have closed all files, and I open s/s#1 and select NO to update links, if I now change the data I am searching for the result is an error condition, (even if the data does actually exist in s/s#2). Some spreadsheets I have created have this problem and some don't. I suspect there is a setting which allows you to cache the data from the linked spreadsheet. Any suggestions? Scott -- scott.auer ------------------------------------------------------...

Unable to Print just one check
Hi, I am in desperate need of help. We were able to print one check at a time until lst night. As far as I know there have been no chges to the settings of My Money. In fact one check was printed last night and after that the program will only allow two or more checks to be printed at a time. When one check is selected to be printed the print option does not appear on the left hand side of the screen as it normally would. The option to print is only displayed once two or more checks have been selected to be printed. Can someone please help?? Thank you!! Maybe. See http://www.b...

Change default # of print copies in Excel
I have an Excel 2003 SP3 document that defaults to 15 copies when printing it. It can be over-ridden, but people forget to do that and print too much. Can the '15' be changed to '1' and saved that way for future printing? How do you do it? Thanks, Stan Milbrath ...

Excel 2008 hyperlinks won=?ISO-8859-1?Q?=92?=t save on .xlsx format, but will save in xls format.
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel xcel 2008 hyperlinks won't save on .xlsx format, but will save in xls format. <br><br>After the workbook is saved and reopened the hyperlink give the error message "Reference is not valid". <br><br>I have cleared all links and hyperlinks and replaced the hyperlinks a number of time with the same results. > Make sure you have applied all the updates top Office. I think this one was <br> > solved in a service pack. <br> > <br> > <br>...

copying data from an unopened file programmatically
Hi, I want to copy data from an excel file to another excel file w/o having to open the source excel file using codings. Is this possible ? tks & rdgs It might be possible using ADO (DAO??), but it's beyond me. Why not just open the workbook, extract the data, and close the workbook. With screenupdating=false, it may not even be noticeable by the user. maxzsim wrote: > > Hi, > > I want to copy data from an excel file to another excel file w/o having > to open the source excel file using codings. Is this possible ? > > tks & rdgs -- Dave Pete...

How can I reference the last data item in a column or row?
I want to display or use the last (most recent) data in either a column or row withour specifically accessing the individual cell. Is there a funcion similar to Min() or Max() that can help me or perhaps another way to solve this question? Thanks See: http://www.xldynamic.com/source/xld.LastValue.html Hope this helps Rowan snappy wrote: > I want to display or use the last (most recent) data in either a column > > or row withour specifically accessing the individual cell. Is there a > funcion similar to Min() or Max() that can help me or perhaps another > way to solve th...

Print BCC field in outlook 2003
So a lot of people say the BCC field never prints, but they are wrong. It will print if you open your sent item and design the form (Tools/forms/design this form) Add the BCC field (under Address fields) and right click on BCC (Properties/validation) to enable printing. Publish the form, and now when you open the message you can print the BCC list. However, I can not figure out how to save this form so that it will apply to all of my sent items. Is this possible? "jasonb" <jason.bordelon@gmail.com> wrote in message news:1194976099.729822.323940@k79g2000hse.googlegroups.c...

Clip Art colors print incorrectly
I am trying to create a certificate using Publisher 2002. I am printing on a hp psc 2110 all-in-one. The pre- designed document prints fine, however when I add a clipart design to it, the colors in the clipart print in incorrect colors. Yellow and red come out blue, blue comes out red. This only applies to the clipart graphics while the rest of the document comes out correctly. This only happens in Publisher, if I copy the same art to a word document it prints correctly. By the way, if I send it to our network color printer, it prints fine, so somewhere between publisher and my pr...

Printing Table headings at the top of every page
This is frustrating me to no end. I have a simple table with column headings and I wish to re-print the column headings at the top of page 2 and page 3. I even see a place in the set-up dialogue box to enable me to do this, but it is greyed out. Why? And, more importantly, how do I tell Excel about the two rows I have devoted to column titles? Do it in File=>PageSetup, not in print preview. then entry would be $2:$3 which you can enter by selecting in the sheet if you do it through page setup under the file menu. -- Regards, Tom Ogilvy "jayceejay" <jayceejay@dis...

VISIO 2007 Calendar
PLEASE HELP - This is driving me crazy!!! I am trying to get data from a SQL Table into a VISIO 2007 calendar. The data is being pulled from a view: alter VIEW [dbo].[LQ_CLIENT_FILE_DELIVERY_CALENDAR] as SELECT CONVERT(varchar(10), DATEADD(dd, DJS.I_EXPECTED_FREQUENCY, DJS.D_LOAD_FINISHED), 101) AS [Date to Display], CONVERT(varchar(10), DATEADD(dd, DJS.I_EXPECTED_FREQUENCY, DJS.D_LOAD_FINISHED), 101) AS [Start date], CONVERT(varchar(10), DATEADD(dd, DJS.I_EXPECTED_FREQUENCY, DJS.D_LOAD_FINISHED), 101) AS [End date], LEFT(DJS.VC_NOTE, 50) AS [Subject] FROM dbo.TBL_LOOKUP_...

OL2007 : Printing problem with multiple mail printing
Hi, i update my OL 2003 to 2007 and now when i try to print some (HTML) email in one time, i've some ok, and some are illegible, font size are decrease to maybe 3pt or 2pt. If y try to print theses mails one by one it's ok. Any solutions ? Thanks in advance ! ...

corner borders (?) printing on my document
I am creating a card on Publisher. On the left side of my document there is a corner (ninety degree angles) appearing on my document as well as small lines at the bottom and top middle. I see them on print preview, but I don't want them to print. How do I remove them. I don't know what they are called and therefore cannot search any Help topics. I am also having problem centering my text on this card. Do these lines have anything to do with that? thank you, thank you, thank you for your help. I am making up wedding invitations on these cards for a friend and am at my wits end. ...

pivot table with external data
I am querying my database and saving it to a recordset...see below...know how do i put that into a pivot? I only know how to write the code for an XL based data source.. Thank you Sub GetAccessData() Dim sh As Worksheet Set sh = Sheets("Sheet2") Dim MyConnect As String Dim MyRecordset As ADODB.Recordset MyConnect = "Provider=Microsoft.ACE.OlEDB.12.0;" & _ "Data Source = C:\Dans_DB.accdb" Set MyRecordset = New ADODB.Recordset MyRecordset.Open "DMA", MyConnect, adOpenStatic, adLockReadOnly For...

Data Flow diagrams
I need data flow diagrams of different modules of GP. e.g. what is the flow of making invoice. If you have the cd set, you can use Explorer to browse it for a folder named SDK. Install the application within it. There are data flow charts for various modules. -- Charles Allen, MVP "Farhan" wrote: > I need data flow diagrams of different modules of GP. e.g. what is the flow > of making invoice. ...

data exchange between two windows
Hey, I've an application with different windows, which have to exchange data between them. I realized this, with windowsmessages and dynamiy allocated memory. Steps: Allocated memory with malloc() Send adress and size of the memory with a message to next window. ReceiveWindow: Read data from memory, free memory with free(). The Problem: Unter Win2000 it works perfect, but under WinNT4 the program won't run. It crashes after the start. Do you have any advice? Do you know an other way to exchange data between the windows? My system: MS Visual Studio 6, Win2000 and WinNT Th...

How to Histogram w/o raw data but histogram Table on Excel
Hi If I have raw data, then I understand the method of doing Histogram with Excel. What is if the Histogram is already presented in a Table and I have to plot the diagram in Excel? eg. (Note "->" means less then the upper value) Range Freq 0->10 2 10->20 12 20->30 15 30->40 10 40->50 1 Can Excel take the above data and plot an Histogram? Is there anything other than Excel? 2005 - > Can Excel take the above data and plot an Histogram? < Yes. Use the Chart Wizard to create a Column chart type. Select the chart, and choose Char...