I have the following code to read into two drop down lists from an
array. The first reads the first row of the array and selects an item
from it. The second list is populated from the entries in the array
column for the item selected from the first row.

It successfully reads the relevant column into "subcomparray(i)" but
the line
".AddItem subcomparray(i)" does not populate the list in the second
dropdown.

Am I missing something obvious or is it not possible to populate one
dropdown based on the selection made in another without re-creating the
controls?

Thanks for any help

For i = 0 To 20
For j = 0 To 19
allcomparray(i, j) =
Sheets("Data").Cells(Range("Components_array").Row + i, _
Range("Components_array").Column + j).Text
comparray(j) = allcomparray(0, j)
Next j
Next i

If cddtparambarcomp.Text = compseltext Then
ActiveCell = compseltext
GoTo CompSelNoChange
Else: ActiveCell = cddtparambarcomp.Text
End If

compseltext = cddtparambarcomp.Text
compselindex = cddtparambarcomp.ListIndex

CompSelNoChange:
For i = 1 To 20
subcomparray(i) = allcomparray(i, compselindex - 1)
Next i

Set cddtparambarsubcomp = CommandBars("Detailed Table Parameter
Menu").Controls(4)
With cddtparambarsubcomp
For i = 1 To 20
..AddItem subcomparray(i)
Next i
..Visible = True
End With


--
micklloyd
------------------------------------------------------------------------
micklloyd's Profile: http://www.excelforum.com/member.php...o&userid=30696
View this thread: http://www.excelforum.com/showthread...hreadid=505260

unless it had something to to with sending this in an email, you don't have
a period in front of AddItem

With cddtparambarsubcomp
For i = 1 To 20
.AddItem subcomparray(i)
Next i
Visible = True
End With

Am I missing something obvious or is it not possible to populate one
dropdown based on the selection made in another without re-creating the
controls?

I am not sure why you ask that question. There is not builtin support for
doing that. Your code is what is using the result of 1 dropdown to
determine what to put in another dropdown, so the only limitation would be
in how you write your code. You don't have to recreate a dropdown control
to add data to it, so the answer would be no, recreating is not a required
step. You should be able to clear the existing control and add new data to
it.

--
Regards,
Tom Ogilvy


"micklloyd" wrote
in message ...

I have the following code to read into two drop down lists from an
array. The first reads the first row of the array and selects an item
from it. The second list is populated from the entries in the array
column for the item selected from the first row.

It successfully reads the relevant column into "subcomparray(i)" but
the line
".AddItem subcomparray(i)" does not populate the list in the second
dropdown.

Am I missing something obvious or is it not possible to populate one
dropdown based on the selection made in another without re-creating the
controls?

Thanks for any help

For i = 0 To 20
For j = 0 To 19
allcomparray(i, j) =
Sheets("Data").Cells(Range("Components_array").Row + i, _
Range("Components_array").Column + j).Text
comparray(j) = allcomparray(0, j)
Next j
Next i

If cddtparambarcomp.Text = compseltext Then
ActiveCell = compseltext
GoTo CompSelNoChange
Else: ActiveCell = cddtparambarcomp.Text
End If

compseltext = cddtparambarcomp.Text
compselindex = cddtparambarcomp.ListIndex

CompSelNoChange:
For i = 1 To 20
subcomparray(i) = allcomparray(i, compselindex - 1)
Next i

Set cddtparambarsubcomp = CommandBars("Detailed Table Parameter
Menu").Controls(4)
With cddtparambarsubcomp
For i = 1 To 20
AddItem subcomparray(i)
Next i
Visible = True
End With


--
micklloyd
------------------------------------------------------------------------
micklloyd's Profile:
http://www.excelforum.com/member.php...o&userid=30696
View this thread: http://www.excelforum.com/showthread...hreadid=505260

Still can't get it. I've modified the code and in fact can pass the
second array to another procedure that creates a new commandbar with a
drop down. This new drop down list always shows the data (from the
passed array)that relates to the item selected in the first drop down
on the original commandbar. I cannot find how to fill the second drop
down on the original commandbar based on the selection madein the
first.

Set cddtparambarsubcomp = CommandBars("Detailed Table Parameter
Menu").Controls(4)
cddtparambarsubcomp = subcomparray()

The above sets the value of "cddtparambarsubcomp" to the correct item
from the second array but the values are not transferred to the second
drop down on the commandbar, which retains the values entered when the
drop down and the commnadbar were created.


--
micklloyd
------------------------------------------------------------------------
micklloyd's Profile: http://www.excelforum.com/member.php...o&userid=30696
View this thread: http://www.excelforum.com/showthread...hreadid=505260

So you confirm that it can be done.

I imagine you problem is your code, but your naming convention appears too
obtuse to me, so I wouldn't even attempt to try to decipher your code.

Seems you just need a bit more due diligence and you should have it.

--
Regards,
Tom Ogilvy



"micklloyd" wrote
in message ...

Still can't get it. I've modified the code and in fact can pass the
second array to another procedure that creates a new commandbar with a
drop down. This new drop down list always shows the data (from the
passed array)that relates to the item selected in the first drop down
on the original commandbar. I cannot find how to fill the second drop
down on the original commandbar based on the selection madein the
first.

Set cddtparambarsubcomp = CommandBars("Detailed Table Parameter
Menu").Controls(4)
cddtparambarsubcomp = subcomparray()

The above sets the value of "cddtparambarsubcomp" to the correct item
from the second array but the values are not transferred to the second
drop down on the commandbar, which retains the values entered when the
drop down and the commnadbar were created.


--
micklloyd
------------------------------------------------------------------------
micklloyd's Profile:
http://www.excelforum.com/member.php...o&userid=30696
View this thread: http://www.excelforum.com/showthread...hreadid=505260

Seems to me I don't need replies with that kind of tone, thanks

--
micklloy
-----------------------------------------------------------------------
micklloyd's Profile: http://www.excelforum.com/member.php...fo&userid=3069
View this thread: http://www.excelforum.com/showthread.php?threadid=50526