How can I create automatic paragraph breaks in a VBA textbox?

Question

I'm currently pulling text from a cell on a worksheet to a textbox on a userform. I don't even know if it's possible, but I ideally want to be able to put a symbol in the cell that separates lines and creates bullet points in the textbox.

For instance, make this information in a cell:

NOTE (SALE): SAID HE CAN ONLY GIVE THAT BIG AMOUNT ONCE A YEAR AND WANTS TO DO IT FOR THEM. JUST STARTED WORKING AGAIN SO PM IS BEST - NOTE (SALE): TRIED TO GIVE THROUGH THE MAIL, JUST BRING UP HIS CARD INFO (2/9/23)

Look like this in the textbox:

• NOTE (SALE): SAID HE CAN ONLY GIVE THAT BIG AMOUNT ONCE A YEAR AND WANTS TO DO IT FOR THEM. JUST STARTED WORKING AGAIN SO PM IS BEST

• NOTE (SALE): NOTE (SALE): TRIED TO GIVE THROUGH THE MAIL, JUST BRING UP HIS CARD INFO (2/9/23)

I also, if possible, want to be able to tab through the different bullet points. Is this even possible?

Answer 1

If you're asking for the textbox to display output as a proper bulleted list (like it does in Markdown), I don't believe that's possible. However, a plain-text list is doable. Textboxes have a Multiline property as well as a Scrollbars property. You can set them manually in the Userform editor or do it with VBA during Userform Initialization:

TextBox.MultiLine = TRUE
TextBox.Scrollbars = fmScrollBarsVertical

For the list creation, if the input is guaranteed to match your example, a simple replacement will add a linebreak at every "-"

txt = ActiveCell.Text   'Pulling from the ActiveCell as an example
txt = Replace(txt, "-", vbNewLine & "-")
txt = "-" & txt         'Add the first "bullet"

However, you'll want to add more text parsing to get the correct output. As a start, you could use InStr(txt,"-") to determine if you should add the dash to the start.

Moving onto the Tab feature, the KeyPress event can be utilized (replace "TextBox1" with your TextBox's name):

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    'Listen for the Tab key
    If KeyAscii = vbKeyTab Then

       'Move the cursor location to the next instance of "-"
       TextBox1.SelStart = InStr(TextBox1.SelStart + 1, TextBox1.Value, "-")
       
       'Suppress the Tab from creating spaces
       KeyAscii = 0
    End If
End Sub

In order for Tab to actually work, you'll have to enable its typical behavior in the Userform. Once again, this can be done manually via editor or with VBA TextBox.TabBehavior = TRUE.