Commandbars - again

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

micklloyd - 26 Jan 2006 10:29 GMT

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

Signature

micklloyd

Reply to this Message

Tom Ogilvy - 26 Jan 2006 12:37 GMT

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.

Signature

Regards,
Tom Ogilvy

> 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
[quoted text clipped - 43 lines]
> Visible = True
> End With

Reply to this Message

micklloyd - 27 Jan 2006 08:42 GMT

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.

Signature

micklloyd

Reply to this Message

Tom Ogilvy - 27 Jan 2006 21:53 GMT

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.

Signature

Regards,
Tom Ogilvy

> 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
[quoted text clipped - 12 lines]
> drop down on the commandbar, which retains the values entered when the
> drop down and the commnadbar were created.

Reply to this Message

micklloyd - 30 Jan 2006 08:32 GMT

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

Reply to this Message