#16
|
|||
|
|||
Glad it helped
|
#17
|
|||
|
|||
Where I am currently stuck....
|
#18
|
||||
|
||||
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 |
#19
|
|||
|
|||
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. Last edited by kiwimtnbkr; 10-21-2020 at 12:57 AM. Reason: edited for clarity and shocking english! |
#20
|
||||
|
||||
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 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 |
#21
|
|||||||
|
|||||||
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:
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:
Quote:
Quote:
Quote:
Quote:
Quote:
Thanks for the reminder! |
#22
|
||||
|
||||
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 |
#23
|
|||
|
|||
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!
|
|
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 |
A non mailing label? | markg2 | Mail Merge | 1 | 12-22-2011 04:07 PM |
Mailing label help needed... | Lisa Cotton | Word Tables | 1 | 04-14-2011 04:49 PM |