Select next available mailing label by row and column?

Purfleet · #1 10-19-2020, 12:38 PM

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.

kiwimtnbkr · #2 10-19-2020, 12:50 PM

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?

kiwimtnbkr · #3 10-19-2020, 11:39 PM

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.

Purfleet · #4 10-20-2020, 01:23 PM

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

kiwimtnbkr · #5 10-20-2020, 08:09 PM

Quote:

Originally Posted by Purfleet

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

kiwimtnbkr · #6 10-20-2020, 11:15 PM

Where I am currently stuck....

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