Excel vba code - identify a dropdown box

[roofprog]

I have a worksheet with about 120 dropdown boxes.Is there vba code which I can use to identify that the current cell contains a dropdown box?

 

[SkipVought]

Hi,

Control obejcts are in the Shapes collection of the Worksheeet Object and are not directly related to Sheet Ranges.

Loop thru the Shapes collection and check the TopLeftCell & BottomRightCell properties.

Why so many dropdowns. That seems WAAAAAAY excessive to me.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[roofprog]

Thanks Skip.

The spreadsheet that I have is used for project pricing.

Materials are contained in a second sheet which is populated from a Sage accounting application using odbc link. The drop downs allow many materials to be selected and the pricing sheet has code to calculate line values when a quantity is entered by the user.

I will try to identify the dropdowns from the information that you have given me.

One other query which you may be able to help me with.

I would like to be able to filter the contents of the dropdown depending on the type of materials being selected. I have coded this so that on clicking into a dropdown, an autofilter is actioned on the sheet2 containing the material list. This works fine in that the sheet2 shows only the filtered materials, but I am unable to populate the dropdown with only the filtered material; I always get a complete list in the dropdown. Any ideas on how to refresh the dropdown?

Thank you ever so much for your help.

 

[SkipVought]

This task can be done with ONE dropdown box, that can be positioned using the Worksheet_SelectionChange event and populated depending on the context (column) selected. That's issue 1.

Issue 2 is related to a combobox list dependent on some previous selection. This can be done with MS Query. faq68-5829.

Perhaps you ought to describe your sheet in more detail. Please, as an example, post 2 rows of relevant data and explain what columns have DDs and what selection in what dd should cause what to happen.

Also where is the list data coming from? What is the structure of that data?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[roofprog]

Many thanks for your help. The identification of a cell shape works well using the intersect method. I have now got a single drop down using a query to populate the data set in a seperate worksheet, and link this to the dropdown.
One small problem is the control of the index line in the dropdown. The dropdown when opened tries to match the previously selected value, or if not previously selected then the first blank entry at the end of the dropdown list.
How can I set (in vba code) the dropdown to always display the first entry in the dropdown list?
Similarly can the dropdown list of elements be restricted so as not to show blanks?

 

[SkipVought]

Exactly what type of control are you using? Control Toolbox, Forms???

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[roofprog]

The dropdown is a form control.

 

[SkipVought]

YourSheetObject.Shapes("YourControlName").OleFormat.Object.Value = 1

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}