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
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
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
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
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
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
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
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
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!