VBA:Printing Sequential Invoices

[BigBas]

I have written some code for a friend of mine, but I cannot test it because I currently don't have a printer attached to this computer. I am confident that it will work, but I would like a second set of eyes to confirm that I havent missed anything.

I have a userform with two textboxes. One of them asks how many copies of the invoice to print out, and the second one asks what invoice number to start with. THe invoice numbers are recorded in Cell I1.

Please Login or Register  to view this content.

Also, for the future, in a situation like this, is there anyway to test if the code would work without a printer? I tried substituting printpreview, but my computer locked up.

[shg]

Looks good to me.

A purist would check that the textboxes are numeric, and then do the conversion to Long explicitly.

I may have misunderstood your intent:

One of them asks how many copies of the invoice to print out, and the second one asks what invoice number to start with.

... but I don't see that you're printing more than one copy of any given invoice.

[BigBas]

Shg:

I'm not sure what you mean by the purist comment.

Also, I am printing out the same sheet with an incrementally higher number each time. Since the situation doesn't permit for a computer on site, they chose to print out a number of invoices in advance and fill them out manually on site. Textbox1 checks the number of invoices to print (lets say 10). Textbox2 checks the number to start with (let say they want to start with invoice number 15, it will print 15-25).

[shg]

I'm not sure what you mean by the purist comment.

Sorry; I meant

Please Login or Register  to view this content.

[BigBas]

Sorry; I meant

Please Login or Register  to view this content.

It's funny you mentioned that. After thinking about it for a minute, I ended up making the same change that you suggested, as well as adding an error handler in case of a printer error. I guess I'm gradually becoming a "purist."

Thanks for the recommendations either way.

Here is the amended code, in case anyone is interested or wants to make suggestions:

Please Login or Register  to view this content.

[solnajeff]

Hi

If you would like to see the output without actuallly printing anything try "Fineprint" which is a great way to preview without actually sending the output to the physical printer.

Regards

Jeff

[royUK]

Hi

If you would like to see the output without actuallly printing anything try "Fineprint" which is a great way to preview without actually sending the output to the physical printer.

Regards

Jeff

You don't need to use any other software. The locking up is caused by the UserForm still being active. Just add Me.Hide at the start of Processing.

[royUK]

I've just had a closer look at your code & I think it only checks the first TextBox. Also you don't use Exit Sub so the problem message will show each time. This way checks each TextBox as you input, you could run the checks from the commandbutton, but I like this way.

Please Login or Register  to view this content.

[BigBas]

Roy:

Thanks for the code. It looks pretty snazzy, even though there are certain parts I don't understand.

1. You use me.hide to close a userform. I have been using Unload Me all along. Is there a difference? Should I use one vs the other?

2. Can you briefly walk me through how your function works. I understand most of it but get confused at the Select Case part. (ie, Case < 0, yet Check Numeric returns True?)

[shg]

When you hide a form, it remains in memory and retains its data, and you can read and write to it. When you unload a form, it's gone; the next time you see it, it will have design-time data.

In many applications, you will find it's desirable to intercept the Close event and check the reason why the form is closing. If the reason is that the X was clicked in the user interface, then you can cancel the close and hide the form instead.

BTW, Roy's code amply illustrates how much detail goes into making a form behave in a simple, intuitive way.

BTW2: Roy, on hiding the form before the PrintPreview -- my Excel lesson for the day, thanks.

[royUK]

The code uses Me.Hide, because if you unload a UserForm any variables set in that Form are lost and you may want to use them later.

I have added some explanation to the Function, hope that explains

Please Login or Register  to view this content.

[BigBas]

Roy:

Thanks for clarifying; it makes more sense to me. What had initially confused me (I think) was the naming structure. The function was called CheckNumeric, yet it returned FALSE if it was numeric, and TRUE if it wasn't. I thought I was going cukoo until I realized that it was done this way to cancel the textbox exit event.

[Leanne44]

I am hoping someone can help me!

I would like to be able to print multiple blank invoices that while printing, change invoice numbers. So basically, I would like to open my invoice template, select print, when it asks how many copies and I enter 100, it prints 1-100. Right now, the macro I am using will do this, but it goes to print screen everytime and when I print it, it asks how many I want to run first, then the print box shows up and asks how many copies and I have to go through the 'how many copies' for every single print. And to do that when printing 100+ copies would be WAY too time consuming.

Here is the macro I am using (I actually think that it is one that royUK created elsewhere that I found online) - I tried to remove the .printpreview and I messed something up lol.

Please Login or Register  to view this content.

[shg]

Please Login or Register  to view this content.

[arlu1201]

Leanne44,

Welcome to the forum.

I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. In order to put code tags, either type [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] at the end of it, OR you can highlight your code and click the # icon at the top of your post window.

Also for future reference, post your question in a new thread.