I dont know where to begin :(

[Meader]

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

 

[JLatham]

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]

One last thing I should have mentioned, I only have Excel 2002. Are all
these features available on that version?

Thanks, you have honestly saved me countless hours trying to figure out this

 

[JLatham]

Take a look at a kind of sample - that may help you some:
http://www.jlathamsite.com/uploads/PCS_FormsRoughedOut.xls

 

[JLatham]

Yes, everything mentioned works all the way back to at least '97. See my
other post for a file you can download that roughs a lot of this out so you
can see how things fit together. It can be a 2-worksheet solution. Could
even be a 1-sheet solution, but I don't recommend it. The 'database' of
stock numbers should be safely out of harms way - you can even hide that
sheet just to keep anyone from going there accidentally.

 

[Meader]

Do I need a username/password to access your website??

 

[Meader]

Wait I figured it out

Thank you very much... again

 

[Meader]

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?

 

[JLatham]

Sometimes I just get frustrated with this place! I replied to this 2 or 3
days ago but system wouldn't acknowledge the input and I was on the road, so
couldn't confirm it worked or not. Obviously not. I'll try again (memory
working hard now to remember what I came up with)...

Since I don't know exactly how you've worked up things so far, I'm going to
assume you created a sheet where people enter the basic information and that
shows the form to be printed all on one sheet. And since I don't know how
it's laid out, I'll just use cell references with names for what they are
being used for such as "cellWithSkid#OnForm" instead of "A1" or "G9"... I
will also assume that the sheet with all of this information is the one
currently selected when you run this macro:

Sub PrintSkidForms()

Dim printFormLoop As Integer

For printFormLoop = 0 To Range("cellWithNumberOfSkids").Value - 1
Range("cellWithSkid#OnForm").Value = _
printFormLoop + Range("cellWithStartingSkid#InIt").Value
ActiveSheet.PrintOut copies:=2
Next

If you need help with this, reply here, or feel free to contact me at
(remove spaces)
HelpFrom @ jlathamsite.com

 

[JLatham]

Lets make that code look more like something that actually has a chance of
running... Needs the End Sub statement to get closer to that status!

Sub PrintSkidForms()

Dim printFormLoop As Integer

For printFormLoop = 0 To Range("cellWithNumberOfSkids").Value - 1
Range("cellWithSkid#OnForm").Value = _
printFormLoop + Range("cellWithStartingSkid#InIt").Value
ActiveSheet.PrintOut copies:=2
Next

End Sub