Printing with Visual Basic Editor Problem

[Guest]

Hey eveyone,

I have a problem, I dont know where to begin and was looking for some
direction.
Here is what my company has asked me to do.

I need to create a method for printing off Pallet Control Sheets (PCS) which
go on our pallets of product, that is tamper-proof and easy to understand.

Here is an example of what a PCS looks like:

stock
code: xxxx-xxx-xx
Brand Name of Product
Organic SALTED Smooth
Peanut Butter Size
Date Code(s) _______ # of Cases: _________
_______ _________
Skid Number: 15
D.O.M:

Now what I would like is for the guys in the plant, who are very computer
illiterate, to be able to open up some sort of program/form/sheet thing which
would then ask them these questions:

Enter the stock code: ( which is 9 digits ) eg. XXXX-XXX-XX
How many skids are there? eg.15
Starting number? eg 3

When they enter the data I want it to generate a form that looks like the
Pallet Control Sheet.

Starting with the first question. When they type in the 9 digit stock code,
I would like it to bring up the 3 lines on the top of the Pallet Control
Sheet which is specific to each product we make. If possible can the 3 lines
printed off be different Fonts/font sizes?

Then I want it to print off the correct number Pallet control sheets due to
number of skids they enter and which number they are starting at. I want 2
copies for every skid number
eg. if its 15 skids of product with starting number 3 then it would print
off 30 PCS with sequencial numbering from 3 to 18. The first two sheets
would say skid 3, then the next two would say skid 4, etc....

Also, if possible, I would also like in the top right hand corner of the
Pallet Control Sheet to print off the stock code in small font.


This is the problem that has been given to me. I like how I always get
chosen for things that are just out of my ability levels, and hence my need
for help.

Does anyone have any idea how I would go about solving/creating this. What
do I need to use, Excel? Word? Access? a combination? I have basic knowledge
on all 3 programs.

If someone could just give me a general outline as to how they would
accomplish this it would be greatly appreciated.

Thank you very much for your time,

Andrew

Excel will work fine for this, my other choice would be Access.

Your starting point is going to be getting all of those 9-digit stock codes
entered into a worksheet. Next to those stock codes, on the same row you
want to enter the 3 items of information associated with each one; one per
cell. The stock codes need to be in the left most column of the group of 4.
The obvious solution is to put stock codes in column A, then the other 3
related entries in B, C and D. This will allow you to use a VLOOKUP()
formula on the sheet you will design to be your PCS.

Yes, Excel allows you to set different font styles and sizes in different
cells on the same sheet.

You then design another sheet to be the PCS that will be printed out. Make
it look the way you want it to, and set the PrintArea property for it to keep
from printing anything other than it that may be on the sheet.

Then what you're (probably) goin to have is a 'button' on that form that
gives a clue like "Start New PCS - Click Here". You can even set its
properties so that even though it's on that sheet, it doesn't show up on the
printouts.

Clicking the button could bring up a userform (created inside of the VB
Editor) that would have input areas for the necessary information (stock #,
Skid #, # of skids) and validate them before continuing. Code would then
simply transfer the stock number to the PCS sheet which would cause the
VLOOKUP() formulas to get the 3 related items and put them where they need to
be on each one, and then it would use the starting skid number and # of
skids/palettes to go through a double-loop to print out the copies.

Similar to that, but without the userform: have a separate sheet that has
input instructions for the information they need to enter and a button that
says "click here to transfer what you typed over to the PCS and print them"
(well, you can probably say it quicker/shorter). They'd enter the
information into appropriate cells on that sheet, and click the button and
pretty much the same process would take place, although the stock code cell
on the PCS sheet could be linked to the cell they type it into.

Come to think about it - you'd only need one sheet for the data entry/PCS
form part of this: some few rows at the top to give instructions and accept
input into the cells, then down below that would be the actual PCS form, and
it would be filling up with info as they type it in in that area. By using
worksheet protection, locked cells and such, you could do it without risk of
them screwing up the form. By setting the PrintArea for the sheet, when the
'print it' button was clicked, only the PCS would be printed, not the extra
stuff on the sheet where they do the data entry.

Meader wrote:

Ok Im stuck again....

I have a question regarding how to get a button on my excel sheet to print
off a certain number of sheets in sequence.

I have the two following lines:

Enter the starting skid #: eg.5
Enter the number of skids : eg 14

And what I would like is for when someone clicks on the button, it will
print off 14 sheets starting from skid number 5, then 6 then 7 etc.

But I would also need 2 copies of each sheet. (2 of 5, 2 of 6...)

My questions are as follows:

What do i need to type in Visual Basic editor to get this to work?

How do I get it to change the number it would print off, like if it says
skid # 5 on the first sheet, how would I get it to change to skid # 6 for the
next?

 

[Roger Govier]

Hi Andrew

I have a couple of templates for producing Pallet labels that I created
when we had our own factory producing frozen food products.
If you want to email me direct, I will send them to you and maybe you
can modify them to suit your needs.

To mail direct remove NOSPAM from my Address

 

[Guest]

Alright, sounds good, I will send you off an email

Thanks

 

[Guest]

Andrew,
Your original post is here:
http://www.microsoft.com/office/com...966f90-3d52-4b6a-a6cb-a69aa3366128&sloc=en-us

You can find that kind of thing in the future by entering "Meader" (w/o
double quotes) in the search box if you're using the Microsoft forums. But
no matter ... I finally replied in that discussion to your request for help
assuming you may be using the workbook/sheet that I whipped up during that
discussion. Sorry for the delay, I've been on the road and tried to provide
a reply a couple of days ago, but apparently the system didn't finish the
process. Of course, by now, you may have found Roger's template and process
more beneficial.