challenge for the braniacs!!! i need help.

[Derrick]

I want to make a macro that will create a dropdown list of steel members that
are acceptable. the easiest way to explain is with an example...
2 sheets: Verticals, Steel
Verticals:
A B C D E F
1 Deflection Steel
2 Allow Actual Needed Description Ix Weight
3 .5 .4 (dropdown1)
4 .32 .25 (dropdown2) 2X3 BAR 3 5
5 .5 1.3 (dropdown3) 4X3 BAR 6 8

Steel:
A B C D
1 Item Description Ix Weight
2 1 1X2 BAR 1 2
3 2 2X3 BAR 3 5
4 3 4X3 BAR 6 8

(Dropdown1)=
-"NONE NEEDED"
(Dropdown2) =
- "2 - 1X2 BAR"
- "3 - 4X3 BAR"
and displays "2" when selected
(Dropdown3) =
- "3 - 4X3 BAR"
and displays "3" when selected

ok. So the point of all this is to have a macro that will go through my
steel sheet after doing my calcs to see if the defl works with the properties
Ix & Weight.. and display a dropdown list in the "Steel Needed" column.
i was thinking something like this: (but i dont know VBA)

C3,C4,C5=

if Defl Actual> Defl Allowable then
for (x=1, to 'Last item on Steel sheet', x++)
'check defl with formula, using item 'x' properties
if 'defl with Steel' < Defl Allowable then
' add to dropdown list
end if
end loop
else Steel Needed = "No Steel Needed"
end if

next:
if item '3 (for example)' is selected then
display '3' in cell (not '3 - 4x3 BAR')
end if

can anyone help? ive posted this before, and nobody's responded..
thanks so much.
Ps. If this is posted twice, its because there was an error posting and i
dont know if it actually posted.

 

[ker_01]

Hi Derrick- a few questions, which might help clarify and get responses from
the community;

(1) in your dropdown examples, you have several marked as "and displays 'x'
when selected". Are you indicating that you just need the first item to be
the default item showing in that cell (a pre-selection, if you will), or are
you saying that when a selection is made, that you want to replace the text
string with an actual numeric value (and if so, why)?

(2) It isn't clear to me (because I'm not familiar with steel engineering)
how this will calculate (or if that is your intention) a new deflection based
on the steel item selected, or how to identify which steel options to use to
populate the drop-down box based on deflection in a particular project (which
I'm guessing is based on the length of the steel, how much weight it needs to
bear, etc). Your code mentions "if 'defl with Steel' < Defl Allowable" so I'm
assuming that 'defl with Steel' is the same thing as "Deflection Actual"
(Column B) but how will your code know that Steel options 3 and 4 produce an
acceptable deflection, but Steel options 1 and 2 don't?

Based on a very limited understanding of what you are trying to achieve, it
sounds like you will need a potentially different list for each drop-down. To
do that, I'd suggest using your code to generate an array of the options for
a particular cell, name it (like a named range), and then assign that to the
cell's data validation (all in code).

Best,
Keith