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,617
gmaxey is just really nicegmaxey is just really nicegmaxey is just really nicegmaxey is just really nicegmaxey is just really nice
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