View Single Post
 
Old 05-14-2020, 09:06 AM
gmaxey gmaxey is offline Windows 10 Office 2016
Expert
 
Join Date: May 2010
Location: Brasstown, NC
Posts: 1,427
gmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the rough
Default

On second thought, as functions are intended to be lasting and perform a "repeatable" process, it might be best to have general function that returns a comma/and delimited list from multiple listbox entries:

Code:
Private Sub CommandButton1_Click()
  MsgBox "Releaseable to " & fcnComma_And_DelimitedList(ListBoxMarking)
End Sub

Private Sub UserForm_Initialize()
  ListBoxMarking.List = Split("NATO,The United Nations,the African Union,Troop Contributing States", ",")
End Sub

Private Function fcnComma_And_DelimitedList(oList As Object) As String
Dim strTmp As String
Dim lngIndex As Long
Dim arrListMembers() As String
  For lngIndex = 0 To oList.ListCount - 1
    If oList.Selected(lngIndex) Then
      strTmp = strTmp & oList.List(lngIndex) & "|"
    End If
  Next lngIndex
  If strTmp <> vbNullString Then
    arrListMembers = Split(Left(strTmp, Len(strTmp) - 1), "|")
    strTmp = vbNullString
    Select Case UBound(arrListMembers)
      Case 0: strTmp = arrListMembers(0)
      Case 1: strTmp = arrListMembers(0) & " and " & arrListMembers(1)
      Case Else
        For lngIndex = 0 To UBound(arrListMembers)
          If Not lngIndex = UBound(arrListMembers) Then
            strTmp = strTmp & arrListMembers(lngIndex) & ", "
          Else
            strTmp = strTmp & " and " & arrListMembers(lngIndex)
          End If
        Next lngIndex
    End Select
    fcnComma_And_DelimitedList = strTmp
  End If
lbl_Exit:
  Exit Function
End Function
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote