View Single Post
 
Old 10-21-2020, 03:19 AM
Guessed's Avatar
Guessed Guessed is online now Windows 10 Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,978
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