Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-15-2020, 11:40 PM
kiwimtnbkr kiwimtnbkr is offline Select next available mailing label by row and column? Windows 10 Select next available mailing label by row and column? Office 2016
Advanced Beginner
Select next available mailing label by row and column?
 
Join Date: Oct 2017
Posts: 69
kiwimtnbkr is on a distinguished road
Default Select next available mailing label by row and column?

Has any one worked out a way, without resorting to Word, to get Excel to print a mailing label by inputting the row and column of the next available label on a standard Avery 14 row / 2 column label sheet.

The addressees are all contained on a single table and would like to be able to print a single 'to' and a single 'from' label.



I've done a forum search and nothing pops up...

Last edited by kiwimtnbkr; 10-16-2020 at 12:44 PM. Reason: Expand explanation
Reply With Quote
  #2  
Old 10-16-2020, 03:12 AM
Purfleet Purfleet is offline Select next available mailing label by row and column? Windows 10 Select next available mailing label by row and column? Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

So you want to print 1 address multiple times? Do you have an example?
Reply With Quote
  #3  
Old 10-16-2020, 12:29 PM
kiwimtnbkr kiwimtnbkr is offline Select next available mailing label by row and column? Windows 10 Select next available mailing label by row and column? Office 2016
Advanced Beginner
Select next available mailing label by row and column?
 
Join Date: Oct 2017
Posts: 69
kiwimtnbkr is on a distinguished road
Default

No - it would one 'to' address label and one 'from' address label printed on demand. Some suppliers get sent to on a regular basis but others only two or three times a year.

No example at this stage as it's only a conceptual idea at this stage, although if it proves doable then I will look to incorporate it into an existing project in order to have a 'one stop shop'.

Can mock an example up if you think it might explain things a bit better?
Reply With Quote
  #4  
Old 10-16-2020, 12:31 PM
Purfleet Purfleet is offline Select next available mailing label by row and column? Windows 10 Select next available mailing label by row and column? Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

i am sure something couldnt be done, but i cant really visuallise exsactly what you are after
Reply With Quote
  #5  
Old 10-16-2020, 01:01 PM
kiwimtnbkr kiwimtnbkr is offline Select next available mailing label by row and column? Windows 10 Select next available mailing label by row and column? Office 2016
Advanced Beginner
Select next available mailing label by row and column?
 
Join Date: Oct 2017
Posts: 69
kiwimtnbkr is on a distinguished road
Default

I've 'cheated' and quickly mocked it up using Word's 'Mailings - Create - Labels' and then pasted it Excel as a picture.

Hopefully this allows you to visualize what I am trying to say - picture worth a thousand words
Attached Files
File Type: xlsx Outer Addressess mockup.xlsx (33.1 KB, 8 views)
Reply With Quote
  #6  
Old 10-16-2020, 02:17 PM
macropod's Avatar
macropod macropod is offline Select next available mailing label by row and column? Windows 10 Select next available mailing label by row and column? Office 2010
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

If you're trying to do this via mailmerge, see: https://www.msofficeforums.com/73079-post6.html
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #7  
Old 10-17-2020, 02:33 AM
kiwimtnbkr kiwimtnbkr is offline Select next available mailing label by row and column? Windows 10 Select next available mailing label by row and column? Office 2016
Advanced Beginner
Select next available mailing label by row and column?
 
Join Date: Oct 2017
Posts: 69
kiwimtnbkr is on a distinguished road
Default

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 View Post
If you're trying to do this via mailmerge, see: https://www.msofficeforums.com/73079-post6.html
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.
Attached Files
File Type: xlsm Mailing Label v2 - 17_10.xlsm (84.1 KB, 9 views)

Last edited by kiwimtnbkr; 10-17-2020 at 02:56 PM. Reason: edited for clarity
Reply With Quote
  #8  
Old 10-18-2020, 12:53 PM
Purfleet Purfleet is offline Select next available mailing label by row and column? Windows 10 Select next available mailing label by row and column? Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

I am not complete up to speed on your form but had a quick play and i think you could add somthing like

Code:
  row = Me.TextBox1
  col = Me.TextBox2
  
Call labels
At the end of the Innerenvelope_Click sub

then in a new sub

Code:
Sub labels()

 Worksheets("ToFrom").Shapes.Range(Array("ToAddressee")).Select
 
 Selection.Copy
 
If col = 1 Then
    Worksheets("Outer envelope").Range("a" & row).PasteSpecial
If col = 2 Then
     Worksheets("Outer envelope").Range("b" & row).PasteSpecial
    End If
    End If
 End Sub

You have created the addresses in a shop, so i think you will have to play with sizes to make it all fit but this does the first copy and paste
Reply With Quote
  #9  
Old 10-18-2020, 09:36 PM
kiwimtnbkr kiwimtnbkr is offline Select next available mailing label by row and column? Windows 10 Select next available mailing label by row and column? Office 2016
Advanced Beginner
Select next available mailing label by row and column?
 
Join Date: Oct 2017
Posts: 69
kiwimtnbkr is on a distinguished road
Default

I've copied the code into the project but I'm not seeing anything happening on the outer envelope worksheet.

I've added some text on the 'Inner envelope' and mocked up on the 'Outer envelope', what address would go where if Row 1 / Column 1 (or should that actually read column A) was input in the UserForm to clarify my poor attempt at explaining what I am hoping can be done.

Would it be better to (is it even possible?), once the address has been copied from Inner envelope Column D, then it is pasted in as an 'Edit Text' for the applicable shape?

Or would it be better to remove the shapes altogether and paste to the address into the required row / column cell?

I am wondering if it would be easier to just say that the To address will always go in column A and that the From address will always go into column C (with column B being the spacer between the labels)?

I am now rambling and just throwing ideas out there to people who are WAY more knowledgeable on what is possible with Excel.
Attached Files
File Type: xlsm Mailing Label v2 - 19_10.xlsm (90.2 KB, 4 views)
Reply With Quote
  #10  
Old 10-19-2020, 01:31 AM
ArviLaanemets ArviLaanemets is offline Select next available mailing label by row and column? Windows 8 Select next available mailing label by row and column? Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

All this is doable without any programming. What you need, is:
1. A way to determine for which companies are labels printed in session. E.g. a column in table on Inner Envelope sheet, where you select from Data Validation List TRUE, or "X" or "print" or whatever;
2. A hidden sheet with table, where Where and To addresses for marked companies are collected (1st marked company, 2nd marked company, etc. You have to estimate max possible number of marked companies, and you need a matching number of rows prepared for this table;
3. Some cell, where you can determine the row of 1st label sheet the addresses must start (i.e. to pass rows where labels are peeled off);
4. A report sheet, with table where 2 columns cells set to match label dimensions, and additional columns (and rows) to ensure the needed spacing between labels. Hard Page must be used to ensure only right number of label rows - it's 7 as I can see from your example workbook - is printed to every label sheet). This table must have enough rows for max number of marked labels even when on 1st label sheet printing starts from last row;
5. In Report Sheet, you use worksheet formulas to read From and To addresses starting from row you marked as starting one for 1st label sheet;
6. You have to define a dynamic Named Range, which includes all and only pages on Report Sheet, where are labels to print;
7. You define a Print Area for Report sheet, and set it equal to Named Range you defined.

It's all!
Reply With Quote
  #11  
Old 10-19-2020, 12:38 PM
Purfleet Purfleet is offline Select next available mailing label by row and column? Windows 10 Select next available mailing label by row and column? Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

i forgot you need the varible declaration at the top

Public ROW As Integer, COL As Integer

I then clicked om, create new label, selected Label 1, Marking 1 and ticked the outer address box then 3 in row and 1 in column

As i said i am not familar with your form or the code, so i assume there is a reason you went for a shape rather than text? Text seem more obvious to me but you call.
Attached Images
File Type: jpg Screenshot 2020-10-19 203600.jpg (25.7 KB, 27 views)
Reply With Quote
  #12  
Old 10-19-2020, 12:50 PM
kiwimtnbkr kiwimtnbkr is offline Select next available mailing label by row and column? Windows 10 Select next available mailing label by row and column? Office 2016
Advanced Beginner
Select next available mailing label by row and column?
 
Join Date: Oct 2017
Posts: 69
kiwimtnbkr is on a distinguished road
Default

The shapes are there more for the visual effect and so I can make sure they are the right size when compared to the real label sheet.

If I understand you right, you're saying that I would be better getting rid of the shapes and just leaving the cells on the outer envelope?

What, if any, changes would needed to insert just the text as you suggested?
Reply With Quote
  #13  
Old 10-19-2020, 11:39 PM
kiwimtnbkr kiwimtnbkr is offline Select next available mailing label by row and column? Windows 10 Select next available mailing label by row and column? Office 2016
Advanced Beginner
Select next available mailing label by row and column?
 
Join Date: Oct 2017
Posts: 69
kiwimtnbkr is on a distinguished road
Default

Have had a chance to have get the code in the right places - should have clicked that having 'Call labels' was meant to be in a module. I originally had it in the UserForm code...whoops!

Couple of things that I hope you can assist with resolving... the 'to address' for the outer envelope label is getting picked up from the ToFrom worksheet when it should be getting picked up from Column D on the Inner Envelope worksheet.

Selecting Column 1 works fantastic but nothing appears if Column 2 is selected?

And there is no 'from address' appearing on the outer envelope label?

Have reattached an updated worksheet with the coding you have kindly assisted with so far.
Attached Files
File Type: xltm Mailing Label v2 - 20_10.xltm (77.0 KB, 10 views)
Reply With Quote
  #14  
Old 10-20-2020, 01:23 PM
Purfleet Purfleet is offline Select next available mailing label by row and column? Windows 10 Select next available mailing label by row and column? Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

I must admit i am no expert when it comes for forms, never really had the need. I dont mind trying to help, but please bear this in mind

Quote:
Couple of things that I hope you can assist with resolving... the 'to address' for the outer envelope label is getting picked up from the ToFrom worksheet when it should be getting picked up from Column D on the Inner Envelope worksheet.
Yes i used the data already populated in the sheet to save going back to 'look' for it in the Inner Envelope sheet. The first 2 lines in the lables sub is where we copy the address - some where in the form there is a lookup to find the correct row to copy the address over. I dont know where that is as i am not familiar with the code, but you code use that if you want to copy from the Inner evelope sheet

Quote:
Selecting Column 1 works fantastic but nothing appears if Column 2 is selected?
I had the end ifs wrong, try

Code:
If Col = 1 Then
    Worksheets("Outer envelope").Range("a" & Row).PasteSpecial Paste:=xlPasteValues
    End If
If Col = 2 Then
     Worksheets("Outer envelope").Range("b" & Row).PasteSpecial Paste:=xlPasteValues
    End If
Quote:
And there is no 'from address' appearing on the outer envelope label?
Again i have not looked at this part as it is more or less a repeat of the other code, just copying a different shape/range
Reply With Quote
  #15  
Old 10-20-2020, 08:09 PM
kiwimtnbkr kiwimtnbkr is offline Select next available mailing label by row and column? Windows 10 Select next available mailing label by row and column? Office 2016
Advanced Beginner
Select next available mailing label by row and column?
 
Join Date: Oct 2017
Posts: 69
kiwimtnbkr is on a distinguished road
Default

Quote:
Originally Posted by Purfleet View Post

I had the end ifs wrong, try...
Thank you! that did the trick for that bit - and I now have a much better understanding of how and where to use End If statements

And awesome help with steering me in the right direction for the rest of it - very much appreciated indeed as I will tinker to get the rest of it working now that I know what code it needs and where to look for it

Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to select multiple criteria from same column? LearnerExcel Excel 1 03-10-2018 07:41 AM
move the milestone label to left of column wilster31 Project 0 07-31-2017 07:58 AM
Error message in pivot when i try drag and placing a column in the row label olybobo Excel 0 05-10-2016 02:05 PM
Select next available mailing label by row and column? A non mailing label? markg2 Mail Merge 1 12-22-2011 04:07 PM
Select next available mailing label by row and column? Mailing label help needed... Lisa Cotton Word Tables 1 04-14-2011 04:49 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 12:17 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft