Select next available mailing label by row and column?

Guessed · #18 10-21-2020, 03:19 AM

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.

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