I have a pivot table that can move the location of a combo box depending on
how many rows it returns. Is there any way to determine which cell the combo
box is in.
Something like:
For x = to beginRange to EndRange
if range("C:6") is Combo then ....

Thanks

It might be easier to look at the combobox and find out where it is.

If the combobox is from the control toolbox toolbar:
msgbox worksheets("sheet2").oleobjects("combobox1").tople ftcell.address

If the combobox (AKA DropDown) is from the Forms toolbar:
msgbox worksheets("sheet2").dropdowns("drop down 1").topleftcell.address

Alan Davis wrote:

I have a pivot table that can move the location of a combo box depending on
how many rows it returns. Is there any way to determine which cell the combo
box is in.
Something like:
For x = to beginRange to EndRange
if range("C:6") is Combo then ....

Thanks

--

Dave Peterson

Thanks Dave,

My problem is that this combo box is on a pivot table. I'm not sure if I can
refer to the pivot table cell and get the cell address. There are a bunch of
comb boxes (which are dimension filters) to choose from.

"Dave Peterson" wrote:

It might be easier to look at the combobox and find out where it is.

If the combobox is from the control toolbox toolbar:
msgbox worksheets("sheet2").oleobjects("combobox1").tople ftcell.address

If the combobox (AKA DropDown) is from the Forms toolbar:
msgbox worksheets("sheet2").dropdowns("drop down 1").topleftcell.address

Alan Davis wrote:

I have a pivot table that can move the location of a combo box depending on
how many rows it returns. Is there any way to determine which cell the combo
box is in.
Something like:
For x = to beginRange to EndRange
if range("C:6") is Combo then ....

Thanks

--

Dave Peterson

I'm not quite sure I understand, but you could cycle through all the comboboxes
and check to see if it is in C6.

Option Explicit
Sub testme()
Dim OLEObj As OLEObject
Dim FoundIt As Boolean

FoundIt = False
With Worksheets("sheet1")
For Each OLEObj In .OLEObjects
If TypeOf OLEObj.Object Is MSForms.ComboBox Then
If Intersect(OLEObj.TopLeftCell, .Range("c6")) Is Nothing Then
'keep looking
Else
FoundIt = True
Exit For
End If
End If
Next OLEObj
End With
If FoundIt = True Then
MsgBox FoundIt & vbLf & OLEObj.Name
Else
MsgBox "not found in C6!"
End If
End Sub

Alan Davis wrote:

Thanks Dave,

My problem is that this combo box is on a pivot table. I'm not sure if I can
refer to the pivot table cell and get the cell address. There are a bunch of
comb boxes (which are dimension filters) to choose from.

"Dave Peterson" wrote:

It might be easier to look at the combobox and find out where it is.

If the combobox is from the control toolbox toolbar:
msgbox worksheets("sheet2").oleobjects("combobox1").tople ftcell.address

If the combobox (AKA DropDown) is from the Forms toolbar:
msgbox worksheets("sheet2").dropdowns("drop down 1").topleftcell.address

Alan Davis wrote:

I have a pivot table that can move the location of a combo box depending on
how many rows it returns. Is there any way to determine which cell the combo
box is in.
Something like:
For x = to beginRange to EndRange
if range("C:6") is Combo then ....

Thanks

--

Dave Peterson


--

Dave Peterson