Drop Down List Value?
Drop Down List Value?
(OP)
How do I test the value of a listbox that I have in a cell? "row" is a running variable inside a do while loop.
Range("row,2").Validation _
.Add xlValidateList, xlValidAlertStop, xlBetween, "=$O$4:$O$7"
If Range(row, 2).Value = "Plate" Then
Cells(row, 9) = Cells(row, 5) * Cells(row, 6) * Cells(row, 7) * Cells(row, 8)
End If
Range("row,2").Validation _
.Add xlValidateList, xlValidAlertStop, xlBetween, "=$O$4:$O$7"
If Range(row, 2).Value = "Plate" Then
Cells(row, 9) = Cells(row, 5) * Cells(row, 6) * Cells(row, 7) * Cells(row, 8)
End If





RE: Drop Down List Value?
Range(row,2).Validation _
.Add xlValidateList, xlValidAlertStop, xlBetween, "=$O$4:$O$7"
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
RE: Drop Down List Value?
Sub AutoWeight()
'
' AutoWeight Macro
' Macro recorded 10/31/2006 by jwilliamson
'
' Keyboard Shortcut: Ctrl+w
'
Dim x As Integer
Dim row As Integer
Dim col As Integer
x = Cells(16, 3) 'currently at 50
row = 4
Do While row <= x
Range(row, 2).Validation _
.Add xlValidateList, xlValidAlertStop, xlBetween, "=$O$4:$O$7"
If Range(row, 2).Value = "Plate" Then
Cells(row, 9) = Cells(row, 5) * Cells(row, 6) * Cells(row, 7) * Cells(row, 8)
End If
If Range(row, 2) = "Angle Bar" Then
Cells(row, 9) = (Cells(row, 6) + Cells(row, 7)) * Cells(row, 5) * Cells(row, 8)
End If
If Range(row, 2) = "Round Bar" Then
Cells(row, 9) = Cells(row, 5) * Cells(row, 3) ^ 2 * Pi / 4 * Cells(row, 8)
End If
If Range(row, 2) = "Tube" Then
Cells(row, 9) = (Cells(row, 3) ^ 2 * 3.1415 / 4 - Cells(row, 4) ^ 2 * 3.1415 / 4) * Cells(row, 5) * Cells(row, 8)
End If
row = row + 2
Loop
End Sub
RE: Drop Down List Value?
Range(row, 2) 'invalid
you would use
Range("B" & row) 'valid
However, when referring to a single cell, the easiest way is usually with "Cells", which requires a row number integer, comma, and column number integer, as in
Cells(row, 2) 'also valid
If you replace each instance of "Range" with "Cells" your code it should work fine.
RE: Drop Down List Value?
Instead of Range(row, 2) you need to use Cells(row, 2). Range will accept only a string as an argument, as in Range("P12").
While we're at it, I can't help giving you some tips:
I would use For row = 4 To x Step 2 instead of a Do...Loop construction.
I would set up the validation only once, inside the spreadsheet, not in the code. You'll find out why when you run the code multiple times.
I like to explicitly use the .Value property when you access the value of a cell, so instead of Cells(row, 9) =, I would use Cells(row, 9).Value =.
Instead of the multiple If's a Select Case statement is much more structured:
Select Case Cells(row, 2).Value
Case "Plate"
Cells(row, 9) = Cells(row, 5) * Cells(row, 6) * Cells(row, 7) * Cells(row, 8)
Case "Angle Bar"
Cells(row, 9) = (Cells(row, 6) + Cells(row, 7)) * Cells(row, 5) * Cells(row, 8)
Case "Round Bar"
Cells(row, 9) = Cells(row, 5) * Cells(row, 3) ^ 2 * Pi / 4 * Cells(row, 8)
Case "Tube"
Cells(row, 9) = (Cells(row, 3) ^ 2 * 3.1415 / 4 - Cells(row, 4) ^ 2 * 3.1415 / 4) * Cells(row, 5) * Cells(row, 8)
Case Else
'if you want to trap invalid input
End Select
Happy programming!
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
RE: Drop Down List Value?
Sub AutoWeight()
'
' AutoWeight Macro
' Macro recorded 10/31/2006 by jwilliamson
'
' Keyboard Shortcut: Ctrl+w
'
Dim x As Integer
Dim row As Integer
x = Cells(16, 3)
row = 4
For row = 4 To x Step 3
Select Case Cells(row, 2).Value
Case "Plate"
Cells(row, 9).Value = Cells(row, 5) * Cells(row, 6) * Cells(row, 7) * Cells(row, 8)
Case "Angle Bar"
Cells(row, 9).Value = (Cells(row, 6) + Cells(row, 7)) * Cells(row, 5) * Cells(row, 8)
Case "Round Bar"
Cells(row, 9).Value = Cells(row, 5) * Cells(row, 3) ^ 2 * 3.1415 / 4 * Cells(row, 8)
Case "Tube"
Cells(row, 9).Value = (Cells(row, 3) ^ 2 * 3.1415 / 4 - Cells(row, 4) ^ 2 * 3.1415 / 4) * Cells(row, 5) * Cells(row, 8)
End Select
Next
End Sub
RE: Drop Down List Value?
MsgBox row
inside the "for" loop. You could also add (as Joerd suggested):
Case Else
MsgBox "Unexpected value in row " & row
The reason for this is that if the value doesn't match one of your cases it won't do anything.
One possibility is that your case doesn't match. According to VBA, the strings "Plate" and "plate" are different. I'm not sure how your validation is set up, but it may be that your cells contain strings that the validation allows but don't match the case of the strings in your code.
RE: Drop Down List Value?
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
RE: Drop Down List Value?
RE: Drop Down List Value?
Thanks for all the help guys.