How to reference Drop Down List value in VBA

  • Thread starter Thread starter Shakil Mian
  • Start date Start date
S

Shakil Mian

Hi,

I have a worksheet that contains a series of drop down lists (cells
B2, B3, B4... Bn). The drop down list contains YES, NO values as
options. I select YES from a list. Then code written in VBA is
executed(when i click the "Check" button) that tries to read values
from the cells that contain the values. When the code finds "YES" a
msgbox pops up with the message "Found: YES". The code does the same
for the next cell down untill a blank cell is found and it stops.

When code tries to inspect the cell that contains the drop down list
the following error occurs - "1004 Application-defined or
object-defined error"

Are there other alternatives to interrogate a cell that contains a
drop down list.

Here is the code.

Public sub ProcessDropDownList()
Dim iSelectedRow As integer
Dim iSelectedCol As integer
Dim sSelected As String

Application.Goto Reference:="FirstDropDownList"

iSelectedCol = ActiveCell.Column
iSelectedRow = ActiveCell.Row

while(ActiveSheet.Cells(iSelectedRow , iSelectedCol) <> ""

'Here is the error - "1004 Application-defined or
object-defined error"
sSelected = ActiveSheet.Cells(iSelectedRow , iSelectedCol )
'

if sSelected = "YES" then Msgbox "Found: " & sSelected

iSelectedRow = iSelectedRow + 1

wend

End sub

Any help will be great
Thanks
Shakil Mian
 
You code:-

sSelected = ActiveSheet.Cells(iSelectedRow ,
iSelectedCol )

Here, sSelected is a string. you're trying to assing it a
range. What you really need it the range's Value.


sSelected = ActiveSheet.Cells(iSelectedRow ,
iSelectedCol ).Value



I re-wrote the code a bit too :)



Public Sub ProcessDropDownList()

Dim iSelectedRow As Integer
Dim iSelectedCol As Integer
Dim sSelected As String
Dim Target As Range
Application.Goto Reference:="FirstDropDownList"

iSelectedCol = ActiveCell.Column
iSelectedRow = ActiveCell.Row
Set Target = Cells(iSelectedRow, iSelectedCol)
Do While Target.Value <> ""

'Here is the error - "1004 Application-defined or
'object-defined error"
sSelected = Target.Value
'

If sSelected = "YES" Then _
MsgBox "Found: " & sSelected

Set Target = Target.Offset(1, 0)

Loop

End Sub



Patrick Molloy
Microsoft Excel MVP
 
Back
Top