-1

I would like to remove comma or period and capitalize the first letter of last name, I can only do with a textbox which I have to insert last name every time

Here is my code for the textbox:

Private Sub Command2_DblClick(Cancel As Integer)
  Text19 = Trim(Text19)

  If Right(Text19, 1) = "." Or Right(Text19, 1) = "," Then 
    Text19 = Left(Text19, Len(Text19) - 1)
  End If

  Text19 = UCase(Left(Text19, 1)) & Mid(Text19, 2)
End Sub

How should the code be changed if I want to apply to a listbox?

Chris
  • 93,263
  • 50
  • 204
  • 189
james
  • 11
  • 7
  • You must delete all those blank lines in your question's code, and if possible, indent code. – Lybren Dec 02 '16 at 19:54
  • I have reformatted your code so it's easier to read. Please try to make it as easy as possible for other users to answer your question. There are some great tips in the [ask] page. I also added the [tag:vba] tag for better visibility. This also lets Stack Overflow use improved syntax highlighting of your code. Finally, I changed your question title. A good title tends to generate more views. – Chris Dec 05 '16 at 03:40

1 Answers1

0

Edited for listbox use. 

Public Sub CapitalizeListBox(PListBox As ListBox)

    Dim i, ListItem As Integer
    Dim Lname() As String
    Dim TempString As String

    For ListItem = 0 To PListBox.ListCount - 1

        Lname = Split(PListBox.ItemData(ListItem), " ") ' Break the name into words
        For i = LBound(Lname) To UBound(Lname) 'for each word, capitalize first letter
            Lname(i) = UCase(Left(Lname(i), 1)) & Mid(Lname(i), 2)
        Next i

        TempString = ""


        For i = LBound(Lname) To UBound(Lname) 'Reassemble the name in the textbox
            TempString = TempString & " " & UCase(Left(Lname(i), 1)) & Mid(Lname(i), 2)
        Next i
        TempString = Trim(TempString)
        PListBox.RemoveItem (ListItem)
        PListBox.AddItem TempString, ListItem
    Next ListItem

End Sub
Lybren
  • 305
  • 1
  • 10