Doflamingo
Board Regular
- Joined
- Apr 16, 2019
- Messages
- 238
Hi all,
I wonder how to find the location of a cell at the exact middle of the cells filled above of it.
Here is the code of the listbox which allows to put in the row 5 all the items (let's call them b) contained in the listbox 2, with a separated column at each item
Dim b%, con
For b = 0 To ListBox2.ListCount - 1
With Cells(5, b * 2 + 3)
.ColumnWidth = 15
.Value = ListBox2.List(b)
.BorderAround
.HorizontalAlignment = xlCenter
.Borders.Weight = 3
If b < ListBox2.ListCount - 1 Then
Set con = ActiveSheet.Shapes.AddConnector(1, .Offset(, 1).Left, _
.Top + .Height / 2, .Offset(, 2).Left, .Top + .Height / 2)
con.Line.Weight = 1
con.Line.ForeColor.RGB = RGB(0, 0, 0)
End If
End With
Next
Below those items, coming from my userform I have a textbox.
Here is the code of the userform with the textbox
Private Sub UserForm_Initialize()
TextBox1.Value = ""
end sub
and here is the code of the commandbutton which is supposed to find the right location of the cell where the text contained in the textbox will be put.
Private Sub CommandButton8_Click()
Cells(7,).Value = TextBox1.Value
End Sub
But that's the point, I don't know what to put in (7,) of the Cells features for the column, because it has to be at the exact middle of all the cells filled above representing the items b of listbox2
Any ideas ?
I wonder how to find the location of a cell at the exact middle of the cells filled above of it.
Here is the code of the listbox which allows to put in the row 5 all the items (let's call them b) contained in the listbox 2, with a separated column at each item
Dim b%, con
For b = 0 To ListBox2.ListCount - 1
With Cells(5, b * 2 + 3)
.ColumnWidth = 15
.Value = ListBox2.List(b)
.BorderAround
.HorizontalAlignment = xlCenter
.Borders.Weight = 3
If b < ListBox2.ListCount - 1 Then
Set con = ActiveSheet.Shapes.AddConnector(1, .Offset(, 1).Left, _
.Top + .Height / 2, .Offset(, 2).Left, .Top + .Height / 2)
con.Line.Weight = 1
con.Line.ForeColor.RGB = RGB(0, 0, 0)
End If
End With
Next
Below those items, coming from my userform I have a textbox.
Here is the code of the userform with the textbox
Private Sub UserForm_Initialize()
TextBox1.Value = ""
end sub
and here is the code of the commandbutton which is supposed to find the right location of the cell where the text contained in the textbox will be put.
Private Sub CommandButton8_Click()
Cells(7,).Value = TextBox1.Value
End Sub
But that's the point, I don't know what to put in (7,) of the Cells features for the column, because it has to be at the exact middle of all the cells filled above representing the items b of listbox2
Any ideas ?
!! , indeed, it allows me to put the value of the textbox at the exact middle, just below the other cells containing the values of listbox2 items b.
