Get Drop Down Value 1

[lb1]

I have a block!!!!
How can I retrieve the value of a DropDown that is in an Excel spresdsheet.
Thanks.

 

[Zathras]

If you mean a combo box placed on a worksheet from the Control Toolbox, then the easiest way is to set the LinkedCell property and read it from that cell.

There are many ways to have a "dropdown" in an Excel spreadsheet. If you are using some other method, please provide more details.

 

[lb1]

Sorry,
I have a drop down named "Drop Down 2" on one worksheet name 'Start'.
I have a piece of VBA code that need the value selected in the Drop down 2.

ValDrop = DropDown2.value

Doesn't work!
Hope this is enough Info, let me know.
Tyhanks.
Louis

 

[SkipVought]

Hi,

This looks like a MS Form. There is a link to a cell that should have the index of the selection. With that index, you can lookup the value in the list that the dropdown refrences.

So if your list range is named MyList and the cell link is named ResultLink, then the value would be

MyValue = Application.Index([MyList],[ResultLink],1)

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Zathras]

At the risk of repeating myself, there are many ways to put a drop-down box on a worksheet.

Assuming that you got yours from the Control Toolbox toolbar (which is the easiest way I know), all you have to do is assign the LinkedCell property. (Use the ListFillRange property to populate the list box) Then just reference the worksheet from your code.

Here is the easy way (assuming the LinkedCell is E3):
[blue]

Sub ComboBox1Easy()
  MsgBox [E3]
End Sub

[/color]
If you really want to do it the hard way, the problem is that when you change the (Name) property in the properties sheet, it really doesn't get changed internally (at least not in the OLE object collection) so you have to remember that "Drop Down 2" is really "ComboBox1". Then you could use something like this (but why would you want to?):
[blue]

Sub ComboBox1Hard()
Dim oComboBox As ComboBox
  Set oComboBox = Worksheets("Start").OLEObjects("ComboBox1").Object
  MsgBox oComboBox.Value
  Set oComboBox = Nothing
End Sub

[/color]
Skip: What kind of combo box are you referring to? I have no idea what you are talking about. Index?? Link??

 

[SkipVought]

From the info given -- that the Name was Drop Down 2 -- it looks like a MS Form control.

Given that, the linked cell has the index value of the drop down selection, from which the list value can be determined.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Zathras]

Got it! Thanks. Never paid much attention to the Forms toolbar. It always seemed kind of limited in functionality, but I see how it could be very useful, especially with a two-dimensional table. Sort of a built-in VLOOKUP capability when used the way you illustrated.

Also, I never tried to use worksheet functions that way (without the "WorksheetFunction" qualifier).

You get a star for all that. Now, if lb1 would only come back and tell us what he is really doing....

 

[lb1]

I had to go away, sorry for the delai.

I just put a sample of what I am trying to accomplish at

http://www.lbconsulting.com/custpriv/testa.xls

I believe it is easy, but is-it?
Thanks for you help.
Louis

 

[Zathras]

Ok. Where to begin...

For the record, you used the Forms toolbar and not the Control Toolbox toolbar. Nothing wrong there, there are trade-offs. (Although I think the Control Toolbox is probably easier to work with.)

Here is what I recommend for your spreadsheet:

Remove "Class1" and "Class2" -- you're not using them and they don't need to be there.

Remove "Module2" -- you're not using it.

Remove "Module3" -- you don't need to process the dropdown click event. Right-click on the dropdown, select "Assign Macro.." and remove the macro assignment -- you don't need it.

Right-click on the dropdown, select "Format Control...", select the "Control" tab and type in "N1" (without the quotes) where it says "Cell link:" While you're at it, change the "Drop down lines:" to 12 to make it easier for the user. (You can use any cell you like, I picked N1 because it was there.)

Replace EVERYTHING in Module1 with the following:
[blue]

Option Explicit

Sub ProcForm()
  MsgBox "Selected Month = " & [N1]
End Sub

[/color]
The only additional refinement I would suggest is to use range names instead of cell references, in which case the ProcForm() macro could look like this:

   MsgBox "Selected Month = " & [SelectedMonth]

assuming the range name "SelectedMonth" is applied to cell N1.

 

[lb1]

Ok,
I got it!
Thanks very Much.
Louis