Hi

I have six TextBox that I'm looping through and at the same time I need to change the value of the cell next to the TextBox

How can I do this

Many thanks in advanc

Ren

Hi Ren

A textbox is not next to any cell, it floats above the spreadsheet on the so
called "drawing layer". So it has no idea what's "next to" because it's
below and it's independent.

Best wishes Harald

"rEN" skrev i melding
...
Hi!

I have six TextBox that I'm looping through and at the same time I need to
change the value of the cell next to the TextBox.

How can I do this?

Many thanks in advance

Ren

"rEN" skrev ...

I have six TextBox that I'm looping through and at the same time I need to
change the value of the cell next to the TextBox.

"Harald Staff" wrote ...

A textbox is not next to any cell, it floats above the spreadsheet on the so
called "drawing layer". So it has no idea what's "next to" because it's
below and it's independent.

I'm not sure it's as simple as that because a textbox has a Placement
(Active X controls from the Controls toolbox) or object placement
(Forms toolbar) property e.g. move and size with cells. So it must
have some regard to its position relative to a cell. I'm not sure
whether there is any practical application for the OP in this, though.

Perhaps the OP's needs can be fulfilled by the textbox's LinkedCell
property. If they need subtly different functionality they could,
assuming an ActiveX textbox, write code to make the required link e.g.
a class with 'WithEvents' Textbox and Range properties.

Jamie.

--

"Jamie Collins" skrev i melding
om...
A textbox is not next to any cell, it floats above the spreadsheet on
the so
called "drawing layer". So it has no idea what's "next to" because it's
below and it's independent.

I'm not sure it's as simple

You're absolutely right Jamie, my reply is oversimplified. But the layer
stuff is real, and the cell relations you mention is somewhere between
impossible to handle and impossible to get to. If I had a reasonable way to
do this, I'd be glad and proud to provide it. Likewise if someone proves me
wrong -those solutions are always fantastic and worth the pain of posting
"impossible" when it's not :-)

Best wishes Harald

One possible indirect approach would be to use a looping technique to try
and find the cell who's Top, Left, (Top+Height), (Left+Width) values will
contain the Top and (Left+Width) values of the Textbox.

cell.Top textbox.Top cell.Top + cell.Height
cell.Left < textbox.Left + textbox.Width < cell.Left + cell.Width

Kinda ugly, but it seems to work.

Troy

Sub FindTextboxCell()
Dim rngCell As Range
Dim ii As Long
Dim lngCol As Long
Dim lngRow As Long

For ii = 1 To 256
Set rngCell = Sheet1.Cells(1, ii)
If rngCell.Left Sheet1.TextBox1.Left + Sheet1.TextBox1.Width Then
lngCol = rngCell.Column
Exit For
End If
Next ii

For ii = 1 To 65536
Set rngCell = Sheet1.Cells(ii, 1)
If rngCell.Top Sheet1.TextBox1.Top Then
lngRow = rngCell.Row
Exit For
End If
Next ii

Sheet1.Cells(lngRow, lngCol).Select
Sheet1.Cells(lngRow, lngCol).Value = "I found it"

End Sub


"rEN" wrote in message
...
Hi!

I have six TextBox that I'm looping through and at the same time I need to
change the value of the cell next to the TextBox.

How can I do this?

Many thanks in advance

Ren