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?

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" wrote:

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?

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" wrote:

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" wrote:

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?

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" wrote:

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" wrote:

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" wrote:

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?

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.

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" wrote:

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" wrote:

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" wrote:

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?

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.

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" wrote:

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" wrote:

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" wrote:

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?