Doflamingo
Board Regular
- Joined
- Apr 16, 2019
- Messages
- 238
Hi all, I'm currently looking the lines of code to link different items coming from 2 different listboxes.
Here is my code that allows to, at least, link items of a single list between them:
Here is the macro applied to the commandbutton that displays the items of the listbox in an excel sheet and add a CONNECTOR between the different items of the listbox. BUT the connector is not linked to the cell of the excel sheet where the items are displayed (1 item = 1 cell of an excel sheet)
Private Sub CommandButton3_Click()
Dim a As Long
For a = 0 To ListBox1.ListCount - 1
With Cells(12, a * 2 + 3)
.Value = ListBox1.List(i)
.HorizontalAlignment = xlCenter
.Borders.Weight = 3
If a < ListBox1.ListCount - 1 Then
Set Con = ActiveSheet.Shapes.AddConnector(msoConnectorStraight, .Offset(, 1).Left, .Height * 4.5, .Offset(, 2).Left, .Height * 4.5)
Con.Line.Weight = 2
End If
End With
Next a
For a = 0 To ListBox1.ListCount - 1
Cells(12, a * 2 + 3) = ListBox1.List(a)
Cells(12, a * 2 + 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
Next a
End Sub
Any ideas ?
Here is my code that allows to, at least, link items of a single list between them:
Here is the macro applied to the commandbutton that displays the items of the listbox in an excel sheet and add a CONNECTOR between the different items of the listbox. BUT the connector is not linked to the cell of the excel sheet where the items are displayed (1 item = 1 cell of an excel sheet)
Private Sub CommandButton3_Click()
Dim a As Long
For a = 0 To ListBox1.ListCount - 1
With Cells(12, a * 2 + 3)
.Value = ListBox1.List(i)
.HorizontalAlignment = xlCenter
.Borders.Weight = 3
If a < ListBox1.ListCount - 1 Then
Set Con = ActiveSheet.Shapes.AddConnector(msoConnectorStraight, .Offset(, 1).Left, .Height * 4.5, .Offset(, 2).Left, .Height * 4.5)
Con.Line.Weight = 2
End If
End With
Next a
For a = 0 To ListBox1.ListCount - 1
Cells(12, a * 2 + 3) = ListBox1.List(a)
Cells(12, a * 2 + 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
Next a
End Sub
Any ideas ?