Return contents of cell at Index position in Source Range of Drop

[TheMath]

I have a macro that assigns the Cell link value for the Drop Down (from Form
Tools, not ActiveX). It merely sets the Drop Down's LinkedCell to the cell
that is under the TopLeft corner of the Drop Down control.

This causes the Index (Integer) of the selected item to be placed in the
LinkedCell. I must find the contents of the cell that is referenced by this
number as an offset into the Input Range of cells that are specified for this
Drop Down control. I must copy that string from the source range to another
cell, but I don't know the VBA code to accomplish this. The part of this
code that assigns the LinkedCell works, but the line after that (the Set
SourceRange) fails with:
Run-time error '438':
Object doesn't support this property or method

Dim SourceData As Range
Dim Val as String
For Each bx In ActiveSheet.DropDowns
bx.LinkedCell = bx.TopLeftCell.Address(external:=True)
' 2 lines of psuedo-code below. What's the real syntax?
Set SourceData = Range(bx.RowSource)
Val = SourceData.Offset(bx.LinkedCell)
Next

Question 1) What is the real VBA code needed to return the contents of the
cell referenced by the Index value that is now stored in the LinkedCell?

Question 2) Where does one find the documentation for the methods and
properties of these controls from the Form Tools menu. I see all sorts of
help that deal with creating drop downs, but little about VBA reference to
their methods and properties.

 

[Peter T]

If I follow is this what you are looking for

idx = Range(bx.LinkedCell).Value
'or more simply
'idx = bx.Value
If idx Then
Val = Range(bx.ListFillRange)(, idx)
End If

One way to get help, put a break on

bx.LinkedCell = bx.TopLeftCell.Address(external:=True)

Press Alt-v s and look at bx (the ref to the dropdown) in Locals, then look
at the relevant properties in help.

regards,
Peter T

 

[TheMath]

Peter:

idx = Range(bx.LinkedCell).Value
'or more simply idx = bx.Value
If idx Then
Val = Range(bx.ListFillRange)(, idx)
End If

I tried both variations of the syntax, and it seems to be ALMOST working.

When I run the macro now, I see some values are sometimes returned, but they
do not correspond to the strings that are actually in the corresponding
range. First, the value returned is always the first entry in one of the
ranges that are set up for the 100 different drop down components, no matter
the real index position of the selected value. And second, the value
returned is from the Input range of a different Drop Down.

To clarify what I'm trying to accomplish: After the macro assigns the value
to the Drop Down's Cell link, the Index value appears in the LinkedCell. I
want to "decode" that to get the string that is stored at that relative
position in the range of cells that are specified as the Input range for the
Drop Down.

To simplify: Since the first running of the macro has assigned all of the
Cell link values (bx.LinkedCell), I have removed that line. I can confirm
that all of these assignments work properly because I have moved the Drop
Downs slightly so I can see the integer appear in the LinkedCell whenever I
change the selection in the Drop Down. So far, so good.

Now I need to retrieve the string from the proper cell in the range.

Note that this range is on a different tab (sheet?) in the same workbook.
Does your code take that into account? There is one tab-page that holds all
of the ranges specified for the drop downs, and though the Drop Downs are on
several tabs, all of the Input ranges are specified on the one single-purose
tab.)

Maybe if I understood this syntax better, I could reason through it:

Val = Range(bx.ListFillRange)(, idx)

 

[TheMath]

Peter:

I found one Drop Down hidden under another, and the macro assigned both of
their LinkedCells as the same cell. Once I got rid of that, then the macro
code you provided started getting closer to what I need.

I've added:

MsgBox "idx = " & idx & " " & bx.Name & " = " & Val

When I run the macro, it all seems to work correctly right up through the
first Drop Down that has a non blank value. It correctly displays no value
(Val = <blank>) for all the Drop Downs before that first one that has a value.

After displaying the correct idx and text for the first non-blank Drop Down,
then it only displays the correct value of idx and bx.Name after that. Val
is blank even though the next 2 Drop Downs have something selected and idx is
shown correctly

Does that smell like anything obvious to you?

 

[Peter T]

I'm sorry, I led you astray!
replace the 'wrong' line

' bx is a ref to a Form's DropDown/combo
idx = Range(bx.LinkedCell).Value
'or more simply
'idx = bx.Value
If idx Then
'' Val = Range(bx.ListFillRange)(, idx) ' wrong
Val = Range(bx.ListFillRange)(idx, 1)
End If

Should work now, assuming of course multiple combo's don't use the same
linked cell as it seems you had at one stage. But even if they did all would
work if you get the index from the .Value property.

If the ListFillRange is in a single column (the combo only lists values in
the first column) you don't need to include the column index, ie (idx) vs
(idx, 1), but safer to include it.

Regards,
Peter T

 

[TheMath]

Peter:

Thank you for your persistence. You code works perfectly now, and your
explanation sheds a lot of light on the way that statement works. So, not
only did you write the important part for me, you helped me along the road
toward learning to be self-sufficient. The more that this process is
repeated, the more people there will be that come here and provide answers
and not just questions.

 

[Peter T]

You are welcome !
Peter T