Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #16  
Old 10-20-2020, 10:41 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


Glad it helped
Reply With Quote
  #17  
Old 10-20-2020, 11:15 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

Where I am currently stuck....
Attached Files
File Type: xltm Mailing Label v2 - 20_10.xltm (86.5 KB, 7 views)
Reply With Quote
  #18  
Old 10-21-2020, 12:07 AM
Guessed's Avatar
Guessed Guessed is offline Select next available mailing label by row and column? Windows 10 Select next available mailing label by row and column? Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

Mike

Firstly, get rid of the shapes on the Outer envelope worksheet. They don't add any value and:
- they make the coding harder
- they won't ever align perfectly with the labels coming through the printer so they will look bad

Are you trying to add a new label row to that sheet each time the 'Create new' button is pressed? As in, the first time we fill in row 1, then row 2, then 3 etc until we hit the maximum of 7. If so, why do you expect the user to work out the next available slot (row/col). The macro should be able to work this out by iterating through the cells until it hits an empty one.

What is the point of the labels? Are you trying to be environmentally conscious and feed the same sheet through the printer each time a label is required until all the slots are used? If so, you will need to wipe the 'already gone' slots because the toner won't stick to the exposed backing and it will kill your printer pretty quickly.
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #19  
Old 10-21-2020, 12:18 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

Shapes removed in the attached workbook. The cells should match the position and size on the actual Avery label sheet - if not then I will adjust as required.

The start row and start column are going to depend on what labels have already been peeled off the sheet. That was the reason I was trying to be cunning with having the start row / column field on the UserForm.

The labels will be printed on demand so the start row / column could be anywhere on the 'Outer Envelope' label sheet.

I would be happy if, for ease of coding, that the printing always started from column 1 but it would be good to be able to select the start row.

There will only be the 2 inner A5 envelope labels (which is the whole Avery L7068 sheet) and the 2 outer envelope labels (2 of the possible 14 available on the Avery L7163 sheet) printed per use of the template. The chances are that the next time the template is used, the to and from addresses are going to be completely different from the previous use. I hope that explains your queries in your previous post?

And for further clarity - the reason for this whole exercise is that we have to send some stuff double enveloped. The outside envelope address doesn't have the personal details of the person who it is for - those details are on the inner envelope.
Attached Files
File Type: xltm Mailing Label v2 - 20_10.xltm (84.5 KB, 8 views)

Last edited by kiwimtnbkr; 10-21-2020 at 12:57 AM. Reason: edited for clarity and shocking english!
Reply With Quote
  #20  
Old 10-21-2020, 03:19 AM
Guessed's Avatar
Guessed Guessed is offline Select next available mailing label by row and column? Windows 10 Select next available mailing label by row and column? Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

I think this is what you are trying to get to. The Labels macro here is searching the find the first empty cell (label position) and putting some text into it. I think you want the 'to address' to go here but you have a problem with where that information comes from. If the userform is open, and the user has made a selection then you can get the address from there. However, you have put the macro outside of the userform and therefore it is not a safe assumption that the userform is open - so you can't harvest this address. An alternative is to get the address from the text box on the ToFrom sheet - this would be a lot easier if you had the address in a fixed cell address instead of a shape.

I make all text in old cells have white text so nothing prints on those cells (assumes you have already printed on those label positions)

Code:
Sub Labels()
  Dim aSheet As Worksheet, aShape As Shape, aRng As Range
  Dim rngEmpty As Range, rngCell As Range
  
  Set aSheet = ActiveWorkbook.Sheets("Outer Envelope")
  Set aRng = aSheet.Range("A1:B7")
  aRng.Font.Color = RGB(255, 255, 255)    'set all cells to white text
  
  'Find the first empty cell
  For Each rngCell In aRng
    If rngCell.Value = "" Then
      Set rngEmpty = rngCell
      Exit For
    End If
  Next rngCell
  
  If rngEmpty Is Nothing Then  'If all cells have content, wipe them all and start again
    aRng.ClearContents
    Set rngEmpty = aSheet.Range("A1")
  End If
  
  'Now write the address to the first empty cell
  rngEmpty.Font.Color = RGB(0, 0, 0)    'make the text in this cell text black
  rngEmpty.Value = "Hello sailor"       'where are you going to get the address from?
  MsgBox "First empty cell was: " & rngEmpty.Address
      
 End Sub
This code avoids the need to ask the user to nominate the first label position. If you wanted the ability to do this, perhaps you could prefill the address of that cell in the userform and let the user edit that if they think it is necessary.

Set your label worksheet to centre the content in the cells vertically so they print in the middle of the label.
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #21  
Old 10-21-2020, 11:15 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

Ugh - I was halfway thru replying and realised that I have done a SHOCKING job in naming the worksheets, totally my mistake and lesson learnt as it's confusing me in replying and no doubt even worse for you trying to decipher what I am trying to do.
The currently named 'Inner envelope' worksheet should have been named 'Addresses' and the currently named 'ToFrom' worksheet should have named 'Inner envelope'.
I can update the names and change the code to reflect the changed names and re-upload if you think that will assist?

Quote:
Originally Posted by Guessed View Post
I think this is what you are trying to get to. The Labels macro here is searching the find the first empty cell (label position) and putting some text into it.
Sort of - because my intention is for this to be a template so the users can't play around and break it, all the addresses will be 'hard coded' in the table that is in the Inner envelope worksheet (and the worksheet 'veryhidden')and any changes to the addresses will be controlled and an updated template sent out to the users.
Column B of the table populates the 'To' address and Column C of the table populates the 'From' address on the 'ToFrom' worksheet - with the macro knowing what addresses are needed for the Inner envelope as the user has selected them on the Userform.
What I am hoping is that, prior to the user clicking the 'Create labels' on the Userform, the Userform To and From address selections would then be able to be used to also populate two labels with the 'To' and 'From' addresses on the Outer envelope and based on the addresses from Column D.

Quote:
Originally Posted by Guessed View Post
I think you want the 'to address' to go here but you have a problem with where that information comes from. If the userform is open, and the user has made a selection then you can get the address from there.
Yes - the user will make all the required selections in the Userform before clicking 'Create labels' and as I have been able to hopefully articulate above, the 'ToFrom' worksheet 'To' and 'From' addresses are getting harvested (I like that term!) from Columns B and C respectively of the Inner envelope table and with the 'To' and 'From' addresses for the two labels on the Outer envelope worksheet getting harvested from Column D.

Quote:
Originally Posted by Guessed View Post
However, you have put the macro outside of the userform and therefore it is not a safe assumption that the userform is open - so you can't harvest this address.
Hopefully answered above?

Quote:
Originally Posted by Guessed View Post
An alternative is to get the address from the text box on the ToFrom sheet - this would be a lot easier if you had the address in a fixed cell address instead of a shape.
We have to send some stuff double enveloped for confidentiality reasons. The outside envelope address doesn't have the personal details of the person who it is for nor the person who sent it, just the simple 'Column D' address details - because of the method of sending, the receiving company knows that the package has to go to a specific department to be dealt with and when the outer envelope is removed then that department knows who in the department needs to deal with it because of the address details harvested from Column B. The joys of company policy...


Quote:
Originally Posted by Guessed View Post
Code:
  rngEmpty.Value = "Hello sailor"       'where are you going to get the address from?
From Column D on the Inner envelope table but it needs to print a 'To' label and a 'From' label with the abbreviated address info.

Quote:
Originally Posted by Guessed View Post
This code avoids the need to ask the user to nominate the first label position.
Because this is a template, that was the reason that I wanted the user to nominate the first label position.

Quote:
Originally Posted by Guessed View Post
If you wanted the ability to do this, perhaps you could prefill the address of that cell in the userform and let the user edit that if they think it is necessary.
Was hoping that Column D on the Inner envelope worksheet would be able to fill this purpose?

Quote:
Originally Posted by Guessed View Post
Set your label worksheet to centre the content in the cells vertically so they print in the middle of the label.
Thanks for the reminder!
Reply With Quote
  #22  
Old 10-21-2020, 08:43 PM
Guessed's Avatar
Guessed Guessed is offline Select next available mailing label by row and column? Windows 10 Select next available mailing label by row and column? Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

Just closing this off after some interactions offline with the Mike...

This issue seems to be resolved now with some of the logic sorted out. The Labels macro now set to take some inputs passed in from the userform.
Code:
'Call the Labels function with some inputs
  Labels sTo:=Me.cbTo.Column(3), sFrom:=Me.cbFrom.Column(3), iRow:=Me.cbRow

Function Labels(sTo As String, sFrom As String, iRow As Integer)
  Dim aSheet As Worksheet, aRng As Range
  
  Set aSheet = ActiveWorkbook.Sheets("Outer Envelope")
  aSheet.Range("A1:B7").ClearContents
  Set aRng = aSheet.Range("A" & iRow)
  aRng.Value = sTo
  aRng.Offset(0, 1).Value = sFrom
      
End Function
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #23  
Old 10-22-2020, 08:02 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

Massive thanks to Andrew for his assistance in resolving this. The solution does exactly what it needs to do, despite me not helping with my Userform design inexperience... some very good lessons learnt for next time!
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 07:11 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