Y/N and print job sequencer

[Lewis Redmond]

Hi folks. (jump to bottom for actual need)


I have a workbook that has four identical "panels" (groups of cells) in
it (on one sheet) that track the serial number of a "box" of devices as
they get placed into individual boxes.

All the data in all the 'panels' (cell array) are identical and have
all the items in the box on the listing.

I print a single A size sheet print job that prints all four panels on
one sheet at one page by one page. That prints perfectly.

Off to the right of the cells, I have two columns beside each pair
(left to right, then down) of 'panels' spaced off to the right side winch
contain Y/N dropdown 'decision boxes'. One for each line entry in the
panels, so my columns are labeled "Panel 1", "Panel 2", etc. so that each
column controls one panel to its left.

As I set each control box to "Y", I have this set to cause the
appropriate line in each panel to become highlighted. This works
perfectly.

Currently, I highlight the first four entries by setting their control
boxes to "Y", then print it, then set them to the next four entries, and
turn the first four back to "N", and print again and so on.

I have to do this a few times to cycle through the 16 odd entries and
make them appear highlighted individually. Then, I cut up the print job
into the individual panels and paste each onto the box that matches the
item in it.

This all works perfectly

What I want to do is automate the "Y" / "N" setting changes and print
jobs so that I can "run" it and have all the pages print with a
sequential highlight selection on them, without me having to set each box
to yes, then no and manually print each four panel page.

The need:

So, essentially, I have two (broken) columns of "Y" / "N" drop down
boxes that are NOT row sequential, and I need a macro that steps through
each set of four, changing them to "Y" and "N" sequentially and stopping
to run a print session at each pair of set toggles.

So it would be a sequence of value set entries for each cell that gets
toggled, and a print run between each pair of Y/N toggles.

That doesn't seem to be a hard script to make. I am not, however, a VB
programmer. Could you help?

 

[Lewis Redmond]

Well? Anyone?


I have a workbook that has four identical "panels" (groups of cells) in
it (on one sheet) that track the serial number of a "box" of devices as
they get placed into individual boxes.

 

[GS]

A few Qs:

1. What significance does the 'highlighting' serve?

2. Could you not just copy the 4 sections to a blank sheet and print
that?

OR

3. Why can't you just print the 4 sections 'as is'?

 

[The_Giant_Rat_of_Sumatra]

A few Qs:

1. What significance does the 'highlighting' serve?

I cut up the tags (panels) into individual "box tags" or labels. The
"box" (a chassis, actually) they come out of has a serial number, as does
each item.

Each item gets individually boxed as it is removed from the chassis.

So, I get the serial number output from the application, and it fills my
spreadsheet (workbook) in fine, and I can highlight and print 4 different
panels, then step out of those and into the next four.

That stack of tags gets compared to the devices by the QC personnel
and once they ensure the match, the cart goes back to shipping to be
packed.

The labels are so that when they get into the field, the engineers will
know which boxes to open to re-populate a given chassis.

2. Could you not just copy the 4 sections to a blank sheet and print
that?

Highlighter fades, and that labor is what I saved by printing
pre-highlighted cells. I can also print to label stock that uses an A
size cut sheet as the baseline and provide four precut panels to print
and peel and stick. I have narrowed the 'panels' such that a single wide
piece of shipping tape will hold them on if printed with plain cut sheet
stock, and inside the label perimeter if label stock gets used.

OR

3. Why can't you just print the 4 sections 'as is'?

They are all identical and carry all the serial numbers that the
original 'box' carried. The highlight is to delineate which item is in
the box it (the tag) is on.


I used named ranges for the Y / N cells as well, so the "for/each"
statement (or the like) should be easier, since it is numeric, per se.

So that pair of columns looks like this

Panel 1 Panel 2

Front
Lite2 Y Lite2 Y
Lite3 Y Lite3 Y
Lite4 Y Lite4 Y
Lite6 Y Lite6 Y
Lite7 Y Lite7 Y
Lite8 Y Lite8 Y
Lite9 Y Lite9 Y
Lite10 Y Lite10 Y
Lite11 Y Lite11 Y
Lite12 Y Lite12 Y
Lite13 Y Lite13 Y
Lite14 Y Lite14 Y

Rear
Lite2 Y Lite2 Y
Lite3 Y Lite3 Y
Lite4 Y Lite4 Y
Lite11 Y Lite11 Y
Lite12 Y Lite12 Y
Lite13 Y Lite13 Y
Lite14 Y Lite14 Y


skip a few rows

Panel 3 Panel 4

Front
Lite2 Y Lite2 Y
Lite3 Y Lite3 Y
Lite4 Y Lite4 Y
Lite6 Y Lite6 Y
Lite7 Y Lite7 Y
Lite8 Y Lite8 Y
Lite9 Y Lite9 Y
Lite10 Y Lite10 Y
Lite11 Y Lite11 Y
Lite12 Y Lite12 Y
Lite13 Y Lite13 Y
Lite14 Y Lite14 Y

Rear
Lite2 Y Lite2 Y
Lite3 Y Lite3 Y
Lite4 Y Lite4 Y
Lite11 Y Lite11 Y
Lite12 Y Lite12 Y
Lite13 Y Lite13 Y
Lite14 Y Lite14 Y

 

[GS]

How are you highlighting? (Normal terminology suggest you select all
the cells. I suspect you mean shading the cells.)

Why are you skipping rows between panel sets? Why not keep the data
contiguous and just increase the first row's RowHeight? It would be
easier to program a solution if there were no blank rows between
panels.

Can you send me your workbook and some sample printouts in PDF format?

gesansomATnetscapeDOTnet

 

[The_Giant_Rat_of_Sumatra]

How are you highlighting? (Normal terminology suggest you select all
the cells. I suspect you mean shading the cells.)

Yes. I use a formula to make conditional formatting, which relies on
the value of the Y N cells.

Why are you skipping rows between panel sets?

Not all devices get removed from the box.

Why not keep the data
contiguous and just increase the first row's RowHeight? It would be
easier to program a solution if there were no blank rows between
panels.

What I can do is create an error routine to continue on the rows that
return an error on the range names that do not exists, and step onward.

Can you send me your workbook and some sample printouts in PDF format?

I can probably send the entire workbook to my mediafire archive, but I
may have to alter some data so that nothing specific as far as the
alpha/numbers is conveyed. It isn't critical data, but I can place
sample data in those locations, no problem.

The print jobs are set to one page wide by one page tall, so they
should look the same on most folks' systems.

It is an early work, so there are no instruction anywhere.

Essentially, I store all of the data for archival purposes, and the
print sheets are just lookups to that data.

So the panels all fill from a drop down box that allows one to select
the main box serial number. When you do that all the data auto fills.

My normal process is to set to Y the first two 'slots', and in panel 3
and 4 the next two, then print, then I set those back to N and set the
next four to Y and print the next sheet. I only have to do this about 8
times and it isn't hard, but I knew that I could likely easily automate
it. The big problem is that I do skip some slots and others never get
selected, but I did allow any given line to be selected for highlighting.

I could make a worksheet that is strictly the processing sheet where I
control the other sheets and their data. That way, I could array the
"selectors" sequentially, and even a simple macro recording session would
likely work. I would then remove my selectors from the print job sheets,
and place them on the management sheet.

I know I probably have an odd paradigm, and do things in an unorthodox
way, but I need feedback like that to evolve, so any comments are
appreciated.

gesansomATnetscapeDOTnet

xttp://www.mediafire.com/?9u5g91rq6tcdt5e

You have to change the xttp back to http.

 

[GS]

Okay, I got your file. At first look I can see the complexity of your
task. In exploring it further I see how it works. I confess that I had
an issue with the UUT dropdown of items in SysList and so I converted
SysList's definition to a dynamic range so it only shows the entries in
the list and no blanks.<g>

On MasterList:
Cell labeled 'System S/N' was named "SysList_Hdr" with local scope.
"SysList" RefersTo: was redefined to be a dynamic range as follows...

=OFFSET(MasterList!SysList_Hdr,1,0,COUNTA(MasterList!$A:$A)-1,1)

I might also add that there's way too much use of global scope for
defined names where local scope would be more prudent. Not criticizing
your work; just stating what's considered 'best practice' for
spreadsheet design by various leading Excel minds.<g>


To solution:
My first Q is what criteria determines which cells get Y or N? I think
that should be the first thing to address since that's what controls
the shading. I'm thinking that changing the UUT could automate the Y/N
factor based on some lookup data.

My logic here is that the macro could cycle through SysList to update
UUT dropdown. This would update your panels with data and respective
shading. Then do a printout for each UUT.

The printout process could be for each individual set of panels (both
sheets) for 8 total, OR each set of panels could be copied to a dummy
(temp) sheet with pagebreaks inserted so there's only 1 printout. This
would happen in the background and so there would be no screen activity
until PrintPreview window displays prior to printout. (I like to
display PP so users can verify the print job, cancel if not what they
expected)

 

[The_Giant_Rat_of_Sumatra]

I might also add that there's way too much use of global scope for
defined names where local scope would be more prudent. Not criticizing
your work; just stating what's considered 'best practice' for
spreadsheet design by various leading Excel minds.<g>

Mainly because of how long it took me. It was a LONG time. :-(

I want an external name create/edit applet where I can define names and
then import the set into a workbook. It should be able to reference the
workbook as if it is open to validate your edits as you go. The current
method is too slow.

Thx for your opinions and help though.

I am beginning to wonder what a recorded macro might look like, and if
I might be able to use some of that auto-scripted structure for what I
need, instead of trying to record the entire sequence.

 

[GS]

The_Giant_Rat_of_Sumatra submitted this idea :

Mainly because of how long it took me. It was a LONG time. :-(

I want an external name create/edit applet where I can define names and
then import the set into a workbook. It should be able to reference the
workbook as if it is open to validate your edits as you go. The current
method is too slow.

Thx for your opinions and help though.

I am beginning to wonder what a recorded macro might look like, and if
I might be able to use some of that auto-scripted structure for what I
need, instead of trying to record the entire sequence.

This looks very doable. What you need is to be able to define criteria
for which panel elements get marked 'Y'. I've been reworking your
sample to better facilitate automation and so would like to know if you
want me to continue toward a solution OR will you figure something out
yourself?.

 

[The_Giant_Rat_of_Sumatra]

The_Giant_Rat_of_Sumatra submitted this idea :



This looks very doable. What you need is to be able to define criteria
for which panel elements get marked 'Y'. I've been reworking your
sample to better facilitate automation and so would like to know if you
want me to continue toward a solution OR will you figure something out
yourself?.

It works fine with multiple copies of the print job worksheets.

I just reset the main serial number field in all the subsequent sheets
to call on the cell in the first sheet. Both the 111 and the 242 print
great by merely highlighting all the sheets involved and sending a single
print job.

I individually highlighted the right lines in each sheet. They are all
set that way now. So, instead of sitting, printing, toggling, and
printing again, I simply made copies, and pre-toggled all the positions.
(I know, I over-explained it)

Works great. If I want a single button/key send off, I could simply
record myself highlighting the sheets and sending the print job and
assign that to a button on each master sheet.

Thanks for your help though. My complicated method may have been a
good exercise in some data handling regimen. Not sure what though.
I will globalize some of the naming stuff as you suggested.

It grew to over 800kB with all the added sheets. No that I do not need
the Y/N toggles (though they are nice), I could shrink it a LOT, since I
can hard code the coloring on those cells now. It would simply be 5
pages of lookups. No great feat there. If I could format the output
just right (pagebreaks, etc.), I could get it all onto one sheet.

That did not seem possible before.

 

[GS]

The_Giant_Rat_of_Sumatra submitted this idea :

I will globalize some of the naming stuff as you suggested.

Actually, I was suggesting there's too many global names that would be
better defined as local names for each sheet. This would allow you to
reuse names for same areas on each sheet. For example, Sys111Num and
Sys242Num name the same area on each sheet used for the serial number
dropdown. They both could be named "SerialNumber" using local scope.
This is just one of several that would make your job much easier, AND
be a blessing if you need to add more sheets down the road.

Anyway, glad you worked something out for now.
Best wishes in your endeavors...