Dropdown from 5 years ago question

[Pierre]

This answer posted in 1998 question would help me greatly, but am
soliciting the mechanics of how to do this:

I would like ANOTHER dropdown to offer the sizes and prices available
from the wood that was chosen in the first dropdown.
1. The database is on another worksheet, however the range was
defined-named and the data validation-list dropdown works fine.
2. The list has 15,000 items, with a total of 28,000 lines(accounting
for duplicates and differences in sizes and costs associated with each
item)
3. IOW the 2nd dropdown should be limited to the items found in the
choice made in the first dropdown.

Thanks for any thoughts.
Peter

From T. Ogilvy:

It is not real clear about what you want.

I understand you want a dropdown box that shows

wood 1
wood 2

What happens after that? Do you want another dropdown to offer the
sizes and prices of available wood based on the wood that was chosen
in
the first drop down box. Where is this happening. Are the drop down
boxes on dialog sheets (Excel 95 or earlier) or userforms (Excel 97)
or
are they on the worksheet (from the Forms toolbox or the Control
toolbox) or are you using Excel 97 and want to use Data Validation
dropdown boxes.

where is the data located to populate the dropdown box. Where is the
vlookup formula.

Please clarify and maybe someone can lend a hand.

If you already have some code, it might be useful to post that.

Regards,
Tom Ogilvy

----------
From: JT[SMTP:[email protected]]
Posted At: Friday, August 28, 1998 10:20 AM
Posted To: programming
Conversation: drop down box
Subject: drop down box

I know nothing about vb programming so I came to the experts... I know
this
is probably simple, but..

I have two ranges wood size
wood 1 2x4
wood 1 4x4
wood 1 4x8
wood 2 2x4
wood 2 4x4 ETC.

First I want a dropdown box that will not show duplicates.. and when
an item
is selected it will run my vlookup formula which picks the size and
price

Also... what is the best (and easiest) VB book out there???


thanks JT

 

[Tom Ogilvy]

Debra Dalgleish has documented how to have dependent lists using validation:


Dependent lists - data validation
http://www.contextures.com/xlDataVal02.html


for completeness, here is the predecessor page:
Datavalidation - top
http://www.contextures.com/xlDataVal01.html


I am not sure it totally applicable here. You might need code that does an
autofilter on your data and loads the second box with the results. Post
back if that is the case. (or you may want to just use an autofilter on
your original data since it does what you want by default).

--
Regards,
Tom Ogilvy

This answer posted in 1998 question would help me greatly, but am
soliciting the mechanics of how to do this:

I would like ANOTHER dropdown to offer the sizes and prices available
from the wood that was chosen in the first dropdown.
1. The database is on another worksheet, however the range was
defined-named and the data validation-list dropdown works fine.
2. The list has 15,000 items, with a total of 28,000 lines(accounting
for duplicates and differences in sizes and costs associated with each
item)
3. IOW the 2nd dropdown should be limited to the items found in the
choice made in the first dropdown.

Thanks for any thoughts.
Peter

From T. Ogilvy:

It is not real clear about what you want.

I understand you want a dropdown box that shows

wood 1
wood 2

What happens after that? Do you want another dropdown to offer the
sizes and prices of available wood based on the wood that was chosen
in
the first drop down box. Where is this happening. Are the drop down
boxes on dialog sheets (Excel 95 or earlier) or userforms (Excel 97)
or
are they on the worksheet (from the Forms toolbox or the Control
toolbox) or are you using Excel 97 and want to use Data Validation
dropdown boxes.

where is the data located to populate the dropdown box. Where is the
vlookup formula.

Please clarify and maybe someone can lend a hand.

If you already have some code, it might be useful to post that.

Regards,
Tom Ogilvy

----------
From: JT[SMTP:[email protected]]
Posted At: Friday, August 28, 1998 10:20 AM
Posted To: programming
Conversation: drop down box
Subject: drop down box

I know nothing about vb programming so I came to the experts... I know
this
is probably simple, but..

I have two ranges wood size
wood 1 2x4
wood 1 4x4
wood 1 4x8
wood 2 2x4
wood 2 4x4 ETC.

First I want a dropdown box that will not show duplicates.. and when
an item
is selected it will run my vlookup formula which picks the size and
price

Also... what is the best (and easiest) VB book out there???


thanks JT

 

[Pierre Cardin]

"I am not sure it totally applicable here. You might need code that
does an autofilter on your data and loads the second box with the
results. Post back if that is the case. (or you may want to just use
an autofilter on your original data since it does what you want by
default)."

Looks like code is more appropriate. Re: autofilter: Each line on the
active sheet has numerous vlookups and if functions imbedded, and
crunches up to 2000 items. . it would consume huge resources to use the
autofilter each time and refresh the screen with the retrieved data, as
well as the time involved.

 

[Tom Ogilvy]

Actually I would have suggested using the autofilter with the code, but if
you think that will be two slow, then you need to pick it up in an array

Assume data on sheet data starting in Cell A1 sizes in column C
prices in column E

comboboxes/wood selected on sheet Input

Sub tester9()
Dim varr As Variant, myArray() As Variant
Dim sVal As String
Dim rng As Range, rng1 As Range, rng2 As Range
Dim i As Long, j As Long
Dim colSize As Long ' column number containing size
Dim colPrice As Long ' column number containing price
Dim ncnt As Long
With Worksheets("Input")
Set rng = .Cells(4, 9).Value 'get wood selection
sVal = rng.Value
' or sVal = .combobox1.Value
End With
colSize = 3
colPrice = 5

With Worksheets("Data")
Set rng1 = .Range("A1").CurrentRegion
Set rng2 = rng1.Columns(1) ' wood category
End With
ncnt = Application.CountIf(rng2, sVal)
ReDim myArray(1 To ncnt, 1 To 2)
varr = rng1.Value

j = 0
For i = 2 To UBound(varr, 1)
If varr(i, 1).Value = sVal Then
j = j + 1
myArray(j, 1) = varr(i, colSize)
myArray(j, 2) = varr(i, colPrice)
End If
Next

rng.Parent.combobox2.List = myArray
' or
'Worksheets("Input").combobox2.List = myArray

End Sub

compiled but not tested