INSERTING A BLANK ROW BETWEEN LISTBOX ROWS

[chazrab]

How would I insert a blank row between listbox rows when displaying the data.
I did this with a textbox but the same code generates an invalid property value
at the beginning. Best explained with images below and the following code

Private Sub ListBox1_Click()
Dim n As Long
n = ListBox1.ListIndex
TextBox1.Value = ListBox1.List(n, 1) _
& vbCrLf _
& vbCrLf _
& ListBox1.List(n + 1, 1) _
& vbCrLf + ListBox1.List(n + 2, 1) _
& vbCrLf _
& vbCrLf + ListBox1.List(n + 3, 1) _
& vbCrLf + ListBox1.List(n + 4, 1) _
& vbCrLf _
& vbCrLf + ListBox1.List(n + 5, 1) _
& vbCrLf + ListBox1.List(n + 6, 1) _
& vbCrLf _
& vbCrLf + ListBox1.List(n + 7, 1) _
& vbCrLf + ListBox1.List(n + 8, 1) _
& vbCrLf _
& vbCrLf + ListBox1.List(n + 9, 1) _
& vbCrLf + ListBox1.List(n + 10, 1) _
& vbCrLf

The vbCrLf carriage return line feed easily enough inserts a space - in a textbox. And

How would I do this same thing, achieve the same blank row but with Listbox1 ? I

I thought images below with the code above that works well - with a TEXTBOX would explain clearly.

The problem is, a textbox has limited functionality. More can be accomplished in this app using a listbox.

Apologies for long post. Thanks very much for anyone's help.

cr

 

[BSALV]

this is done in 3 lines, but you can combine everything in 1 line, but that 'll be less readable

     With ComboBox1                                             'your combobox, textbox, ...
          arr = Application.Transpose(.List)                    'read the list of the 1st combobox in an array
          s = Join(arr, "||")                                   'make a string and add 2 pipes as separator
          b = Split(s, "|")                                     'split on that pipe
          ComboBox2.List = b                                    'apply to a(nother) combobox, listbox, ...
     End With

 

[chazrab]

HI BSALV -
I just read your code but have not tested yet because your code refers to
a Combobox1(With ComboBox1). I'm working with a Listbox(Listbox1) if that
makes any difference. I'll replace Combobox1 with 'Listbox1' in your code above
and put this in the userform's initialize event as soon as I get through typing this.

I'm excited because of the simplicity of your code, and, if it will insert a blank row
between Listbox1 rows instead of these cumbersome carriage line feeds I used.

Please get back to me if I'm reading this incorrectly.

Thanks for all your help.
cr

 

[BSALV]

that should work, it just did something similar in a userform of mine

Private Sub UserForm_Initialize()
     c_01.List = Sheets("kladblad").ListObjects(1).DataBodyRange.Columns(2).Value     'add data to 1st listbox

     c_01.List = a
     arr = Application.Transpose(c_01.List)                     'read the list of the 1st combobox in an array
     s = Join(arr, "||")                                        'make a string and add 2 pipes as separator
     b = Split(s, "|")                                          'split on that pipe
     CB_Add.List = b                                            'apply to a(nother) combobox, listbox, ...
     '
End Sub