Looping through a range of cells

[rEN]

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

 

[Harald Staff]

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

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.

 

[Jamie Collins]

"rEN" skrev ...

change the value of the cell next to the TextBox.

...

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.

--

 

[Harald Staff]

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

 

[TroyW]

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

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.