Adding the word 'and' to a list in a message box

Question

I need help getting started on the code for adding the word 'and' to a message box after the user has selected items from a list box to make the message box grammatically correct. Possibly removing the last comma too.

Private Sub cmdSelect_Click()
Dim IntIndex As Integer, strSelectedHHItems As String
For IntIndex = 0 To lstHouseHoldItems.ListCount
    If lstHouseHoldItems.Selected(IntIndex) Then
        strSelectedHHItems = strSelectedHHItems & "," & " " & lstHouseHoldItems.Column(0, IntIndex)
    End If
Next
strSelectedHHItems = Right(strSelectedHHItems, Len(strSelectedHHItems) - 1)'remove beginning space
MsgBox "You have selected" & (strSelectedHHItems)
End Sub

I am an amateur, I know it will require the use of some intrinsic functions like Mid or Len but I can't think of how to do it. Your assistance is greatly appreciated.

Search for "ms access find last comma and replace with and" ;) — ComputerVersteher, Jan 21 '20 at 21:16
Does this answer your question? [Replace only last occurrence of match in a string in VBA](https://stackoverflow.com/questions/24394833/replace-only-last-occurrence-of-match-in-a-string-in-vba) — June7, Jan 21 '20 at 21:28

score 1 · Accepted Answer · answered Jan 21 '20 at 21:29

1

Once you know the location of the last comma, just string together your new string:

loc = InStrRev(strSelectedHHItems, ",")
strSelectedHHItems = Mid(strSelectedHHItems, 1, loc - 1) & " and " & Mid(strSelectedHHItems, loc + 2)

answered Jan 21 '20 at 21:29

Brian M Stafford

8,483
2
16
25

Adding the word 'and' to a list in a message box

1 Answers1