×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Drop Down List Value?

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

RE: Drop Down List Value?

The cell's value is the value of the listbox, so your code looks OK to me, except for a syntax error in the first line: don't use quotes around the row,2 in the Range() statement:
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?

(OP)
Hmmm its still not working.  I think the range function beleives its two different cells with just a comma inbetween.  The .value function is also not highlighted when I enter the code which leads me to believe I have the incorrect function.  Here is my full code in case its something other than the 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" and "Cells" do not use the same arguments.  "Range" requires a string with A1 style referencing.  In your case, instead of

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?

Got it.
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?

(OP)
Ok so it works but only for the top cell or row 4 its not filtering down.  The rows I am now using are 4,7,10,13,16 ect, so I am using a step 3 instead of 2.  Here is the new code.  Anyone know why this won't filter down correctly?

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?

One way to help debug your code and see what's going on is by using message boxes.  For example, if you want to see whether the value of "row" is getting incremented correctly you can add the line

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?

What do you mean by "filtering down"? We were talking about validation, weren't we?

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

RE: Drop Down List Value?

(OP)
Validation is taken care of, all of the cases work but they only work in the first row.  It doesn't fill the rest of the spreadsheet, only the first row. I'll try the message boxes and see if I can figure it out.  Thanks.

RE: Drop Down List Value?

(OP)
Hahaha great idea for the message boxes. It turns out I just didn't have enough coffe this morning and had the row/column switched for variable x.

Thanks for all the help guys.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources