Y/N and print job sequencer
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? |
Y/N and print job sequencer
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. 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? |
Y/N and print job sequencer
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'? -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Y/N and print job sequencer
On Sat, 02 Apr 2011 00:21:45 -0400, GS wrote:
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 |
Y/N and print job sequencer
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 -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Y/N and print job sequencer
On Sat, 02 Apr 2011 03:33:37 -0400, GS wrote:
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. |
Y/N and print job sequencer
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(MasterLi st!$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) -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Y/N and print job sequencer
On Sat, 02 Apr 2011 14:12:20 -0400, GS wrote:
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. |
Y/N and print job sequencer
The_Giant_Rat_of_Sumatra submitted this idea :
On Sat, 02 Apr 2011 14:12:20 -0400, GS wrote: 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. Well, you did ask...! You're welcome.<g 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?. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Y/N and print job sequencer
On Sun, 03 Apr 2011 23:53:02 -0400, GS wrote:
The_Giant_Rat_of_Sumatra submitted this idea : On Sat, 02 Apr 2011 14:12:20 -0400, GS wrote: 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. Well, you did ask...! You're welcome.<g 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?. 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. |
Y/N and print job sequencer
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... -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
All times are GMT +1. The time now is 11:45 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com