Finding the location of a specific cell

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 ?:confused:
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
And how did you fill the listbox2?

You have a list that fills the listbox, then that list you pass to the cells of the sheet. Now you want to modify one of those cells?
Can you explain your process step by step?
 
Upvote 0
Hi Dante,

There is a textbox linked to a commandbutton that fills the listbox. Then another command button which displays in an excel sheet all the items contained in the listbox in the same row, with connectors for each items.

But here is the link of what I have activate the command button of the listbox

https://www.dropbox.com/s/vv1hrndfo2mftvu/What I have.png?dl=0

and here what I would like to obtain

https://www.dropbox.com/s/9nu9ypwopwudbgl/What I want.png?dl=0

So as you can see, in the screenshots, the main problem remain to find the exact location of the cell containing the textbox value, at the exact middle and below the items b of the listbox2,

I imagined something like that, but it doesn't work

Private Sub CommandButton8_Click()
For b = 0 To ListBox2.ListCount - 1
Cells(8, (b * 2 + 3) / 2).Value = TextBox1.Value
End Sub

Any ideas ?:confused:
 
Upvote 0
With odd numbers it would look like this:

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:104.55px;" /><col style="width:76.04px;" /><col style="width:104.55px;" /><col style="width:76.04px;" /><col style="width:104.55px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:center; ">a1</td><td > </td><td style="text-align:center; ">a2</td><td > </td><td style="text-align:center; ">a3</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td > </td><td style="text-align:center; ">Textbox Value</td><td > </td><td > </td></tr></table> <br /><br />




But with even numbers it would look like this:

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:104.55px;" /><col style="width:104.55px;" /><col style="width:104.55px;" /><col style="width:104.55px;" /><col style="width:104.55px;" /><col style="width:104.55px;" /><col style="width:104.55px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:center; ">a1</td><td > </td><td style="text-align:center; ">a2</td><td > </td><td style="text-align:center; ">a3</td><td > </td><td style="text-align:center; ">a4</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td > </td><td > </td><td style="text-align:center; ">Textbox Value</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr></table> <br /><br />



Try this:

Code:
Private Sub CommandButton8_Click()
    qty = WorksheetFunction.RoundUp((ListBox2.ListCount * 2 - 1) / 2, 0)
    With Cells(7, qty + 2)
        .ColumnWidth = 15
        .Value = TextBox1.Value
        .BorderAround
        .HorizontalAlignment = xlCenter
        .Borders.Weight = 3
    End With
End Sub
 
Upvote 0
Hello Dante,

Thank you very much for your reply, it works :)!! , 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.

Right now I have this with your code, and with the condition my list box 2 contains 5 items

https://www.dropbox.com/s/uvg353oywizulrw/Cells of the textbox at the exact middle.png?dl=0

and the next step is to obtain the right connectors :), to get something like that

https://www.dropbox.com/s/rby1bdc0u8xn2vk/What I'm looking for.png?dl=0

But I think that I'm going to begin another thread :)

Thanks again Dante !!

Kind regards :)
 
Upvote 0

Forum statistics

Threads
1,223,908
Messages
6,175,306
Members
452,633
Latest member
DougMo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top