Loop through ActiveX Combo boxes

Hi there,

I have a spreadsheet with 400 rows and I am currently using ActiveX combo boxes to track the Status of a prospect (5 options to select), the options for which are populated from data I dumped in column AN using ListFillRange under Properties.

I would first like to have the Status combo boxes appear only if there is data in column A of the corresponding row.

I would also like to add code so that on any change event in the Status combo box, if the choice is "Dead lead" then a another combo box on the far right in the "Dead Lead: Cause" column becomes visible (see attached picture). My problem is that right now these combo boxes are not linked in any way, so how do i know that on row 23 the Status combo box is ComboBox36 and if "Dead lead" is selected, the new combo box in the "Dead leads: Cause" column is ComboBox173, etc.

My data (excluding headers) are in rows 3-400. So if I could possibly run something like this I believe it would work for me (code is wrong but you should understand what I'm looking for):

Please Login or Register  to view this content.

But "ComboboxC" is not possible as it does not cycle through ComboBox3, ComboBox4, up to 400 as I would like it to.

Then my next question is what Sub would this go under? Logically I think it would go under "Private Sub Worksheet_Change(ByVal Target As Range)" so after any change on the worksheet it would check to see if the change was in Column A, and if so run that loop.

I would very much appreciate help achieving what I am looking for, or suggestions as to how I can make this more efficient if possible (without adding forms as the target audience for this file has enough trouble using Excel so I would like to just clean up what I do have).