Display all the items of a listbox (userform) in specific merged cells ?

[Doflamingo]

Hi all,

I try to design an automate structure/organizational chart and I'm just at the Beginning of my project

First, Here is the code of the List Box with the button of the macro that displays all the items contained in the listbox


Private Sub CommandButton_Click()

Dim I As Long

For I = 0 To ListBox1.ListCount - 1

Cells(1, I + 1) = ListBox1.List(I)

end sub

All the items of the listbox are displayed in a page of an excel spreadsheet in the same row in different columns, like that :
Item A (one column) Item B (one column) Item C (one column) etc.

but what I would like is to have all the items displayed like that ;
item A in a merged cell (2 colums), Empty column, item B in a merged cell (2 colums), Empty column, item C in a merged cell (2 colums), etc.

Any ideas ?

 

[Fluff]

This will enter the values in every other column

   Dim i As Long
   For i = 0 To ListBox1.ListCount - 1
      Cells(1, i * 2 + 1) = ListBox1.List(i)
   Next i

However I would STRONGLY advise against using merged cells, as they will only come back to haunt you.

 

[Joe4]

Here is an alternative to using merged cells that if you are just merging across columns in single rows, will give you the same visual effects without all the issues: https://www.atlaspm.com/toms-tutori...er-across-selection-instead-of-merging-cells/

 

[Doflamingo]

Thank you so much Fluff !!

Indeed it allows me to displays all the item of the listbox (item a, item b, item c etc.) almost like I wanted:

Item A (one column), Empty column, Item B (one column), Empty column, item C (one column), Empty column etc.

I've found another lines of code that allows me to merge in two column the last item of the listbox. Here is the code

Private Sub CommandButton4_Click()


Dim i As Long
Dim MyRange As Worksheet
a = 1


For i = 0 To ListBox1.ListCount - 1
Cells(1, i + 1) = ListBox1.List(i)

Cells(i, i + 3).Select

Set MyRange = Sheets("Sheet5")
With MyRange
.Range(.Cells(1, a + 3), .Cells(1, a + 4)).Merge
End With

But what I would like is to combine your code and this one, maybe a little bit modified to get this;

item A in a merged cell (2 colums), Empty column, item B in a merged cell (2 colums), Empty column, item C in a merged cell (2 colums), etc.

Any ideas ?

Thanks again for your answer

 

[Fluff]

Any ideas ?

yes, DO NOT use merged cells.
They play havoc with VBA, filtering, sorting, among other things.
Have a look at the link supplied by @Joe4

 

[Doflamingo]

Thanks a lot too Joe4, I didn't know that.

I've learn something new today

 

[Doflamingo]

Hi all,

Indeed, I think it would be wiser to give up the idea of merged cells,

Given that the final goal is to automate a structure chart,

here is the beginning of my code:

Dim i As Long
For i = 0 To ListBox1.ListCount - 1
Cells(5, i * 3 + 3) = ListBox1.List(i)

Cells(5, i * 3 + 3).Select


With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With

With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

ActiveSheet.Shapes.AddConnector(msoConnectorStraight, 144, 136.5, 216, 208.5). _
Select
Selection.ShapeRange.ShapeStyle = msoLineStylePreset15
Selection.ShapeRange.IncrementLeft -24
Selection.ShapeRange.IncrementTop -45
Selection.ShapeRange.ScaleWidth 0, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 1.25, msoFalse, msoScaleFromTopLeft



Next i

I've succeeded to put a connector to one of the item of the listbox, the next step and goal is to put connector to all the item of the listbox and... the next step will be link a specific connector between 2 items of an item coming from listbox A and one item coming from listbox B.

Any ideas ?

 

[Fluff]

As this is now a completely different question , you will need to start a new thread.
Thanks

 

[Doflamingo]

Hello @Joe4,

I relaunch you about the link you gave me.

It's great to ''merge'' something horizontally but it drives me crazy about how to merge it vertically ...

Any ideas

 

[Joe4]

It's great to ''merge'' something horizontally but it drives me crazy about how to merge it vertically ...

Any ideas

I have never seen a suitable replacement for vertical merging.
I suppose you could just play around with row height, wrapping text, and vertical centering, and might be able to do whatever it is you are trying to accomplish.
I just make it a point to never merge cells, ever.