Test if cells are selected

[Lisa]

I have a VSTO Excel app where I need to test each row in a range to
see whether the user has selected the cell in column D in that row,
and carry out an operation if they have.

But I'm getting some very erratic results. I tried using For Each
cell as Cell in ActiveWorksheet.Selection.Cells, but that didn't
work. So I went to this:

Dim _col As Integer
Dim _row As Integer
For cellCount As Integer = 0 To .Application.Selection.cells.count -
1
_col = .Application.Selection.cells(cellCount).Column
_row = .Application.Selection.cells(cellCount).Row + 1

The "+ 1" on the row makes no sense to me, but it was off by a row if
I didn't do that.

The problem is, when I select multiple cells in column D, I get weird
results. Two contiguous ones works fine. But if I pick four
contiguous ones, it only sees the first and third. Actually, it
depends on the order in which I select them. And if they aren't
contiguous, it gets even weirder.

Is there a simpler way to do this? A way where instead of going
through the selection and testing the cells to see if they're in the
right column, I can go down that column and check to see if each cell
is selected? I haven't been able to find a Selected or IsSelected
property on cells.

Thanks,
Lisa

 

[Guest]

Lisa,

Try this. It will print into the immediate window which row in column D
(Column 4) is selected.


Dim c As Object

For Each c In Selection
If c.Column = 4 Then
'Do someting
Debug.Print "Row: " & c.Row
End If
Next


Please rate this response when you get a change.

Good Luck

 

[Dave Peterson]

In VBA, I'd use something like:

with selection
if intersect(.cells, .parent.range("d:d")) is nothing then
'no cells in column D
else
'some cells in column d
end if
end with

 

[Lisa]

Thanks, but this doesn't address my problem.

Lisa

 

[Lisa]

Let me be more specific. I have a VSTO app using an Excel template.
I select four cells (G12, H14, H20 and J15, in this example), and run
this code:

For i As Integer = 0 To
BudgetUpdate.Globals.shBudgetUpdate.Application.Selection.Cells.Count
- 1

MsgBox(BudgetUpdate.Globals.shBudgetUpdate.Application.Selection.Cells(i).Address)
Next

I get four MsgBox results popping up, reading, in order: $G$11, $G$12,
$G$13, $G$14.

It's consistent, too. If I select G8, E14, G15 and I16, it returns $E
$13, $E$14, $E$15, $E$16. Every time, it takes the leftmost column,
and starts with the cell just above the one I selected in that
column. And it seems to think that all of the rest of the selected
cells are immediately beneath it.

Is this a known bug? Is there a workaround?

Lisa

 

[Tim Williams]

Lisa,

A selection with non-contiguous sections has "Areas" - if you loop through these it might help.

for each a in selection.areas
for i=1 to a.cells.count
msgbox a.cells(i).address
next i
next a

 

[Dave Peterson]

I don't see this as a bug. .cells(#) doesn't cycle through the cells in the
range. It uses the first cell and then offsets from there.

If you want to cycle through the range:

dim myRng as range
dim mycell as range
set myrng = activesheet.range("g12,h14,h20,j15")

for each mycell in myrng.cells
msgbox mycell.address
next mycell

======
or you could loop through the addresses:
dim iCtr as long
dim myAddr as variant
myaddr = array("g12","h14","h20","J15")
for ictr = lbound(myaddr) to ubound(myaddr)
msgbox activesheet.range(myaddr(ictr)).address
next ictr