Commandbars - again

[micklloyd]

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

[Tom Ogilvy]

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[micklloyd]

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.

[Tom Ogilvy]

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[micklloyd]

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