Can I determine in which cell is a listbox postioned?Or how to get the listboxes in a certain cell?

[vlado_varna]

Hello,

I tried to find ,whether this was discussed before,but couldn't find
anything.I will be thankful for any ideas.
I have a task related to transformation of Excel to XML.
And I encounter this specific problem:
I have coulpe of listboxes (created from the Forms toolbar,bot ActiveX
controls).

My problem is to find out ,in which cell is positioned each listbox.
The list boxes are not linked to cells.But rather I need the cell
,where they are positioned.

In general it will be also a solution for me if I can get the listboxes
positioned in a given cell ..let's say for example in cell A1.

I try with :
Range("A1").Activate
MsgBox "Selected index " & _
Selection.ShapeRange(1).ControlFormat.ListIndex

But it seems this is not the correct way.

What I notice is that a List Box is assigne more or less to the
cell,where the left corner of the listbox is.At least the listbox moves
by default with this cell.

Is it possible for example to select a range within the sheet and then
to get all shapes,positioned in the range.That would solve my problem.

Thanks in advance ..
Any ideas are welcomed

Best regards

Vlado

 

[vlado_varna]

Sorry ,just to clarify...the listboxes are not ActiveX list boxes.They
are simple Excel controls.(If this has any meaning to the question)

 

[Tom Ogilvy]

for each bx in Activesheet.listboxes
msgbox bx.TopLeftCell.Address
next

 

[vlado_varna]

Wow Tom..thanks a lot...
I was looking for this in microsoft MSDN ,but couldn't find it...
GREAT .Thanks a lot!! This completely helps me to implemnt,what I need.

Just for information,because it is interesting to me:
I see,that I can use this to make it in the oppsoite direction...e.g If
I have a cell H10 for example....to check ,if there are listboxes in
the cell...but first I have to loop through all listboxes in the
sheet...is it possible to avoid this?
Suppose ,you want to get all forms,that are position in H11..let's say
listboxed...combo boxes...buttons....
is there a straight forward method for this?

Kind regards

Vlado

 

[Tom Ogilvy]

Nothing built in comes to mind. If you have xl2002 or later (I think this
was introduced in xl2002) you can possibly cobble something together using

RangeFromPoint

See the Excel VBA help for details.

But looping doesn't seem to be that big of a deal to me. You could loop the
shapes collection and test the type of shape.

 

[vlado_varna]

Tom,Thanks again!!!
Kind regards and all the best!!

Vlado