vba syntax

[Derrick]

hi! soo.. i would like to know the code for:
-in a for loop, for a particular row, finding a cell.
ie. if the for loop is on the 2nd row, return the value in the cell("a2"),
if 3rd row, ("A3").. etc
-creating a dropdown list in code.
ie. this is for when (in my for loop) i find an acceptable 'item', it will
be added to a dropdown list.
so if A2,3,4 is ok, and a1,a5 are not, the dropdown list will look
like :
"(Value from A2)
(Value from A3)
Value from A4)"

this is gathered from my for loop, and the process should be triggered when
a specific cell is changed
can you help?

 

[Joel]

See comments in the code below. Put the code in the VBA sheet where you data
is located. (Not a module). Worksheet change must be in the VBA sheet
corresponding to where your data is located

Code does the following
1) Look for data in column A that is under 10
2) Copies these values to column IV which is the validation list
3) Creates a validation cell in B1
4) changing data in B5 will trigger the macro to create a new validation
list. It will delete any validation list that already exists in B1.




Sub worksheet_change(ByVal target As Range)
'only create validation list if cell B5 gets changed.
If Not Application.Intersect(target, Range("B5")) Is Nothing Then
RowCount = 1
NewRow = 1
'go down column A until no more data is found
Do While Range("A" & RowCount) <> ""
Data = Range("A" & 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 Data < 10 Then
Range("IV" & NewRow) = Data
NewRow = NewRow + 1
End If
RowCount = RowCount + 1
Loop

'Now create a validation list (dropdown)
LastRow = NewRow - 1
'B1 will be the validation cell

With Range("B1").Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:="=$IV$1:$IV$" & LastRow

.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
'display 1st item in the validation list
Range("B1") = Range("IV1")
End If


End Sub

 

[Derrick]

hi joel! sorry for not responding earlier... long weekend.. microsoft
discussion pg isn't working either....

thanks for your help so far! its almost perfect. i was wondering if i could
make a few adjustments to the code.
1st: there are 2 diff sheets, one with calcs and one with the data to add to
the list - what's the code for referencing another sheet?
2nd: can we modify this so that i can have a column of validated lists - one
on every row in my table, so that i can have rows of these calcs on the same
page, and not one per page
3rd: if there are no Data values that satisfy my requirement.. ie Data < 10,
an error appears. can we modify this to say "No Suitable Material"?

thanks again!

 

[Joel]

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.

The validation list and the validation cell must be on the same worksheet.
The source data can be on a different sheet. There are restriction in
accessing cells when using a worksheet change function. You cannot write or
select cells in other worksheets. You can read data from other worksheets.

I made the Data row being checked starting at column A of the Datasheet for
the validation list in Row 2. column B for Row 2, etc. I made the
validation list IV for Row 1 data and decreemted one column for each row.


Sub worksheet_change(ByVal target As Range)

set DataSht = Sheets("Sheet1")


'only create validation list if cell B5 gets changed.
If Not Application.Intersect(target, Columns("B")) Is Nothing Then

'Validation list in Row 2 gets data from column A
'1 ("A") = 1 + 2 - 2 = 1
DataCol = Columns("A").column + target.Row - 2

'256 ("IV") = 256 - 2 + 2 = 1
ListCol = Columns("IV").column - target.Row + 2

RowCount = 1
NewRow = 1
'go down column A until no more data is found

with Datasht
Do While .Cells(RowCount, DataCol) <> ""
Data = .Cells(RowCount, DataCol)
'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 Data < 10 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 <> 1 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)
End if
End If


End Sub

 

[Derrick]

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]

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]

oh man. you are the best. im working through it to see what needs to be
changed to fit my spreadsheet.. but so far its working well. haha yea i did
post it alot - it was gonna be the piece de resistance in my spreadsheet, so
i wanted it badly.

i have a question about the target. lines - what is it? i can't find it in
the help menu, and im curious to know where it gets is values from.

Thanks,

 

[Joel]

Target is a variable name. You can make it anything like XYZ. The worksheet
change function has one parameter and most people use the convention to use
Target.

 

[Derrick]

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]

Simple. when you write to the worksheet you are triggering the worksheet
event a 2nd , 3rd, 4th, 5th, ......... Time

Put the following at the begining of the change macro and at the end

at beginning
Application.EnableEvents = False

at end
Application.EnableEvents = True

 

[Joel]

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]

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]

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.