Put a break point in the code by clicking the Intersect statement with the
mouse. then Press F9.


Go back to worksheet and change a cell in column E after row 8. The code
will stop at the break point. Now step through code using F8. Yo can see
variable values by hovering mouse over varaibles. Also yo can add watch
points by highlighting a variable and right click.. Then selecxt add watch
and press OK on dialog.

The code will run when any cell in column E affter row 8 is changed
according to your last posted changes.

"Derrick" wrote:

lol thanks. ive changed the code a bit.. so now it checks to see if defl/
stress is over first before even doing anything.
however, i have a few problems left:
1) nothings being written into my validated list except "select steel",
which means im getting the message "none needed" at the bottom of the page...
im thinking its because of the Cells(newRow,ListCol) = DATA line. ive tried
switching = data to = range("C"&RowCounter), which should give me the size of
teh steel, but its still not working. ive then changed it to the Cells. to
Worksheet("Horizontal").Cells can u let me know if something else is funky?
2) as of right now the only way to update my 'steel needed' cell is by
changing E8. can i make it so that i can change multiple cells and it will
update with any change? ie.. change E8, G8... and it will update 2 times
again, thanks.

"Joel" wrote:

I would put the code to get the data from the worksheet after the Intersect
statement. Silly to execute all that code and then not do anything if the
change is not in column E.

If Not Application.Intersect(Target, Columns("E")) Is Nothing Then

Ix1 = Range("H" & Target.Row)
Ix2 = Range("P" & Target.Row)
Iy1 = Range("F" & Target.Row)
Iy2 = Range("N" & Target.Row)
Sx1 = Range("I" & Target.Row)
Sx2 = Range("Q" & Target.Row)
Sy1 = Range("G" & Target.Row)
y2 = Range("O" & Target.Row)
Length = Range("X" & Target.Row)
BSMaxX1 = Range("AL" & Target.Row)
BSMaxX2 = Range("AN" & Target.Row)
BSMaxY1 = Range("AV" & Target.Row)
BSMaxY2 = Range("AX" & Target.Row)
DeflMaxX = Range("AJ" & Target.Row)
DeflMaxY = Range("AT" & Target.Row)
If Range("AD" & Target.Row) = "1/4 Points" Then
a = Length / 4
ElseIf Range("AD" & Target.Row) = "1/6 Points" Then
a = Length / 6
Else
a = Length / 8
End If


Set DataSht = Sheets("Steel")



"Derrick" wrote:

hey joel!

man its looking good. Im having problems though with one line:

Cells(1,ListCol) = "Select Steel"

this is my code:

Private Sub Worksheet_Change(ByVal Target As Range)

Ix1 = Range("H" & Target.Row)
Ix2 = Range("P" & Target.Row)
Iy1 = Range("F" & Target.Row)
Iy2 = Range("N" & Target.Row)
Sx1 = Range("I" & Target.Row)
Sx2 = Range("Q" & Target.Row)
Sy1 = Range("G" & Target.Row)
y2 = Range("O" & Target.Row)
Length = Range("X" & Target.Row)
BSMaxX1 = Range("AL" & Target.Row)
BSMaxX2 = Range("AN" & Target.Row)
BSMaxY1 = Range("AV" & Target.Row)
BSMaxY2 = Range("AX" & Target.Row)
DeflMaxX = Range("AJ" & Target.Row)
DeflMaxY = Range("AT" & Target.Row)
If Range("AD" & Target.Row) = "1/4 Points" Then
a = Length / 4
ElseIf Range("AD" & Target.Row) = "1/6 Points" Then
a = Length / 6
Else
a = Length / 8
End If


Set DataSht = Sheets("Steel")

If Not Application.Intersect(Target, Columns("E")) Is Nothing Then
If Target.Row = 8 Then
ListCol = Columns("IV").Column - Target.Row
Cells(1, ListCol) = "Select Steel"
RowCounter = 1
NewRow = 2

With DataSht
Do While .Range("C" & RowCounter) < ""
IxStl = .Range("D" & RowCounter) < ""
IyStl = .Range("F" & RowCounter) < ""
SxStl = .Range("E" & RowCounter) < ""
SyStl = .Range("G" & RowCounter) < ""
DeflXStl = 1
If DeflXStl < DeflMaxX Then
DeflYStl = 1
If DeflYStl < DeflMaxY Then
BSX1 = 1
If BSX1 < BSMaxX1 Then
BSX2 = 1
If BSX2 < BSMaxX2 Then
BSY1 = 1
If BSY1 < BSMaxY1 Then
BSY2 = 1
If BSY2 < BSMaxY2 Then
Cells(NewRow, ListCol) = DATA
NewRow = NewRow + 1
End If
End If
End If
End If
End If
End If
Loop
End With

If NewRow < 2 Then
LastRow = NewRow - 1
Set ListRange = Range(Cells(1, ListCol), Cells(LastRow, ListCol))
With Target.Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:="=" & ListRange.Address

.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Target = Cells(1, ListCol)
Else
Target = "None Needed"
End If
End If
End If

End Sub

right now its a lot of nothing. i have to still put formulas in. but the
basics are there, and it should be operational, but i can't seem to make it
work. can you help?

"Joel" wrote:

I read the your request for a STEEL spreadsheet in the past but therequest
where asked a little differently and didn't have the time to answer them.

I made some minor changes to the code. The old code I was assuming the
Steel table was different for each validation list. Now I see it is the
same. The validation will be different for each validation list. I still
don't understand what inputs you are going to use to calculate the
deflection. I assume you know hwat you are doing. I see you have made a lot
of VBA requestts in the past.


Sub worksheet_change(ByVal target As Range)

Set Datasht = Sheets("Steel")

Allowable = target.Value

'only create validation list if cell B5 gets changed.
If Not Application.Intersect(target, Columns("A")) Is Nothing Then
'ignore changes to header row
If target.Row < 1 Then
'256 ("IV") = 256 - 2 + 2 = 1
ListCol = Columns("IV").Column - target.Row + 2
'Put Select Steel into cell initially
Cells(1, ListCol) = "Select Steel"
RowCount = 1
NewRow = 2
'go down column A until no more data is found

With Datasht
Do While .Range("A" & RowCount) < ""
IX = .Range("C" & RowCount)
Weight = .Range("D" & RowCount)
'if data is less than 10 put it into a validation range
'I used column IV for my validation list which
'is the dropdown list
If IX < Allowable Then
Cells(NewRow, ListCol) = Data
NewRow = NewRow + 1
End If
RowCount = RowCount + 1
Loop
End With

'if new row = 1 then there is no data, don't make validation list
If NewRow < 2 Then
'Now create a validation list (dropdown)
LastRow = NewRow - 1
'B1 will be the validation cell

Set ListRange = Range(Cells(1, ListCol), Cells(LastRow, ListCol))
With target.Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:="=" & ListRange.Address

.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
'display 1st item in the validation list
target = Cells(1, ListCol)
Else
target = "None Needed"
End If
End If
End If


End Sub


"Derrick" wrote:

im not quite sure either. I have posted this question before.. (and no-one's
attempted it) but i'll paste my long explanation of what i wanted lol. it
should make things clear... but the majority of what i want you've helped
with.
k here goes:
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)

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
end if

k there u go
thanks again!

"Joel" wrote:

I'm not sure I did exactly what you need. I'm a little confused at which
sheets have the data list, which sheet has the validation Cell, and which
sheet the cell that triggers the worksheet change is located.