Employee Sorting form to email.

[Justinjc79]

Hello, I am making a form that has a list of employees pulled from a cell range. I am able to put them into 2 sperate categories in a user form that can move individually or all remaining between the 3 list boxes. The 4th panel is a preview of an email that is generated after the manual sorting process and the current source of my issue. The email preview is a Text box and I have the VBA coded to a button like :

txprv.value = "Hello!" & vbCrLf & vbCrLf & "We currently have " & Sheet1.Range("B2").Value & " in CORR with " & Sheet1.Range("B3").Value & _
" in total!" & vbCrLf & "We currently have " & Sheet1.Range("B1").Value & " in Processing!" & vbCrLf & "Please move to Accounting if you run out of work." _
& vbCrLf & vbCrLf & "CORR" & vbCrLf & Sheet1.Range F1:XX & vbCrLf & vbCrLf & Sheet1.Range F1:XX

The values in red is the data I am trying to add. I was able to script it so the results from the 2 sorted results list in the F column and G column but am struggling to have it take only the populated cells that vary. since the range currently runs from 1-30 based on number of names total and the amount could change variably. This is how it currently looks altogether including adding an array to determine how many cells in the column have data and have tried to use that value to add the names. If not I get the names added but it has a huge gap of unused cells.

Below is my current coding with "CORR" group filter attempt, and the myStr2 left for "Processing" displays the names from the 2nd list, but with all the spaces.
Private Sub CommandButton16_Click()

Dim myArray1 As Variant
Dim myArray2 As Variant
Dim myStr1 As String
Dim myStr2 As String
Dim x As Long
Dim y As Long
Dim a As Integer
Dim b As String
Dim c
Dim d As Long
Dim e As String
Dim f
Dim br1 As String
Dim br2 As String
br1 = Sheet1.Range("B7").Value
br2 = Sheet1.Range("B8").Value
a = Sheet1.Range("F30").End(xlUp).Row
b = Sheet1.Range("F" & a).Value
c = ListBox2.List
d = Sheet1.Range("G30").End(xlUp).Row
e = Sheet1.Range("G" & d).Value
f = ListBox3.List
'CORR NAMES IN COLUMN F
Sheet1.Range("F1").Resize(UBound(c) + 1, 1).Value = c
'PNDJ NAMES IN COLUMN G
Sheet1.Range("G1").Resize(UBound(f) + 1, 1).Value = f
'Populate range 1

myArray1 = Range("F1:F30").Value

For x = LBound(myArray1, 1) To UBound(myArray1, 1)
myStr1 = myStr1 & myArray1(x, 1) & vbCrLf
Next x
'Populate range 2
myArray2 = Range("G1:G30").Value
For y = LBound(myArray2, 1) To UBound(myArray2, 1)
myStr2 = myStr2 & myArray2(y, 1) & vbCrLf
Next y

txprv.value = "Hello!" & vbCrLf & vbCrLf & "We currently have " & Sheet1.Range("B2").Value & " in CORR with " & Sheet1.Range("B3").Value & _
" in total!" & vbCrLf & "We currently have " & Sheet1.Range("B1").Value & " in Processing!" & vbCrLf & "Please move to Accounting if you run out of work." _
& vbCrLf & vbCrLf & "CORR" & vbCrLf & (br1 & ":" & br2).value & "Processing" & vbCrLf & vbCrLf & myStr2

I have looked for similar issues here and tried working them in but they don't seem "compatible" when I run it and either gives an error or shows nothing. Any assistance is greatly appreciated, thank you!

 

[DanteAmor]

Hi and welcome to MrExcel board!

Check if the following is what you need:

Private Sub CommandButton16_Click()
  Dim i As Long
  Dim b1 As String, b2 As String, b3 As String
  Dim list2 As String, list3 As String
  
  b1 = Sheet1.Range("B1").Value
  b2 = Sheet1.Range("B2").Value
  b3 = Sheet1.Range("B3").Value
  
  For i = 0 To ListBox2.ListCount - 1
    If ListBox2.List(i) <> "" Then
      list2 = list2 & ListBox2.List(i) & vbCr
    End If
  Next
  For i = 0 To ListBox3.ListCount - 1
    If ListBox3.List(i) <> "" Then
      list3 = list3 & ListBox3.List(i) & vbCr
    End If
  Next
   
  txprv.Value = "Hello!" & vbCr & vbCr & _
    "We currently have " & b2 & " in CORR with " & b3 & " in total!" & vbCr & _
    "We currently have " & b1 & " in Processing!" & vbCr & _
    "Please move to Accounting if you run out of work." & vbCr & vbCr & _
    "CORR" & vbCr & list2 & vbCr & list3
End Sub

RESULT: Notice how in listbox2 and listbox3 I have spaces, but when passing the data to the textbox the spaces no longer exist:

--------------------------
This is another result:

Private Sub CommandButton16_Click()
  Dim i As Long, k As Long
  Dim b1 As String, b2 As String, b3 As String
  Dim list2 As String, list3 As String
  Dim nMax As Long
  Dim arr As Variant
  
  b1 = Sheet1.Range("B1").Value
  b2 = Sheet1.Range("B2").Value
  b3 = Sheet1.Range("B3").Value
  
  nMax = WorksheetFunction.Max(ListBox2.ListCount, ListBox3.ListCount)
  ReDim arr(1 To nMax, 1 To 2)
  
  For i = 0 To ListBox2.ListCount - 1
    If ListBox2.List(i) <> "" Then
      k = k + 1
      arr(k, 1) = ListBox2.List(i)
    End If
  Next
  k = 0
  For i = 0 To ListBox3.ListCount - 1
    If ListBox3.List(i) <> "" Then
      k = k + 1
      arr(k, 2) = ListBox3.List(i)
    End If
  Next
  
  For i = 1 To UBound(arr)
    list2 = list2 & arr(i, 1) & vbTab & vbTab & arr(i, 2) & vbCr
  Next
   
  txprv.Value = "Hello!" & vbCr & vbCr & _
    "We currently have " & b2 & " in CORR with " & b3 & " in total!" & vbCr & _
    "We currently have " & b1 & " in Processing!" & vbCr & _
    "Please move to Accounting if you run out of work." & vbCr & vbCr & _
    "CORR" & vbTab & vbTab & "Processing" & vbCr & list2
End Sub

If neither of the 2 options is what you need, then put an image of what you have in each listbox and another image of what you expect in the textbox.
That way it will be more explicit to know what you need and not be guessing the result.

 

[Justinjc79]

Yes, thank you this is exactly what I needed, I knew I was making it more complicated then I had to!