The attached workbook might assist better in seeing if what I want to do is actually doable in the first place. Note that the 'ToFrom' sheet is protected but there is no password, you should be able to just 'unprotect' it.
When the user form opens (and the guts of the code to make it run is attributable to @Guessed - thank you Andrew!), the 'To' and 'From' fields are populated with Column A data from the table found on the 'Inner Envelope' worksheet. The code then pulls the matching data from Column B for the 'To address' on the 'ToFrom' worksheet and the matching data from Column C for the 'From address' on the same worksheet.
A play with the user form will make up for the poor explanation above...
What I would like to see, if it's doable, is when the tick box in the 'outer packaging' frame is selected and the row and column number of the next available blank label is input (see the outer envelope worksheet which I have done up to represent a sheet of Avery address [L7183] labels) is that the matching 'To address' data is pulled from the appropriate row of Column D and placed in the 1st available blank label and that the matching 'From address' data is also pulled from the appropriate row of Column D and placed in the 2nd available blank label.
End game is that the Row x / Column B 'To address' on the 'ToFrom' worksheet matches the Row x / Column D 'To Address' filled in on the first selected blank label and that the Row x / Column C 'From Address' on the 'ToFrom' worksheet matches the Row x / Column D 'From Address' filled in on the second blank label.
All of the individual shapes on the 'outer envelope' worksheet have been given an Alt Text name of 'OutLabeln' where 'n' is their equivalent label number on the Outer Envelope worksheet - if that helps with coming up with a solution.
NOTE - The individual shapes are there more for the visual effect and could be deleted if necessary to leave just the cell which is sized to the same size as the actual label.
Quote:
Originally Posted by macropod
|
I would REALLY like to try and avoid using Mailmerge unless it is seamless for the user and the label printing 'just happens' after they have selected and filled in the appropriate data in the Userform.