Get value from a drop down box on a spreadsheet

[Guest]

I have a regular drop down control on a spreadsheet (i.e. NOT an ActiveX) and
I want to get the text that currently appears in the window. This is the type
of control that does not allow yI am sure it is much easier than I'm making
it out to be, but I can't seem to retrieve it. How is it done?

Thanks in advance.

 

[Guest]

Sorry, I got interrupted mid-stream, my OP should read:

I have a regular drop down control on a spreadsheet (i.e. NOT an ActiveX) and
I want to get the text that currently appears in the window (i.e. selected).
This is the type of control that does not allow you to edit the box itself,
only to select an item from the drop down. I am sure it is much easier than
I'm making
it out to be, but I can't seem to retrieve it. How is it done?

 

[Tom Ogilvy]

With Activesheet.Dropdowns("Drop Down 1")
itm = .List(.ListIndex)
End with
msgbox Itm

 

[Guest]

Thanks Tom, however, I still have an issue. The user is forced to make a
selection from this drop down ("Drop Down 1") on a sheet. Once a selection is
made, the choice is to be written into column "A" on the row in which the
cell pointer currently resides.

The problem is, if the drop down already contains "acquatics", for example,
and the user opens the drop down and clicks on "acquatics" as their choice,
nothing happens. But when the user selects something else, then selects
"acquatics" it works. Do you know what I'm doing wrong? My function follows:

Public Function ControlSSDropDownChange()
'POPULATE CELL WITH SHEET CONTROL CONTENTS
Call SheetProtection(ActiveSheet.Name, False)
With ActiveSheet.DropDowns("Drop Down 1")
Cells(ActiveCell.Row, 1) = .List(.ListIndex)
End With
Call SheetProtection(ActiveSheet.Name, True)
End Function

Thanks in advance, once again, for the help.

 

[Dave Peterson]

Could you just reset that dropdown?

Option Explicit
Public Function ControlSSDropDownChange()
'POPULATE CELL WITH SHEET CONTROL CONTENTS
Call SheetProtection(ActiveSheet.Name, False)
With ActiveSheet.DropDowns("Drop Down 1")
Cells(ActiveCell.Row, 1) = .List(.ListIndex)
.ListIndex = 0
End With
Call SheetProtection(ActiveSheet.Name, True)
End Function

===

I've never assigned a Function to a control like this. I've always use a sub.
(The function worked fine, though. I just thought it was interesting.)