Referencing a form control drop down value

[hallingh]

Hi all. I have a form control combo box, with 3 items in hte drop down: "Select Period" "Q1FY10" and "Q2FY10"

I want to use vba to search a bunch of lines of code and find the value of the drop down, but I don't know how to reference it. This was what I just tried, but it didn't work:

PeriodDropDown.Value

Obviously, the drop down name is "PeriodDropDown"

Any help you can give me is greatly appreciated. Thanks!

Hank

 

[hallingh]

OK, I've changed my reference to this:

Forms!PeriodDropDown.Value

which is all I can get (at least that makes sense to me) from a google search, but it still isn't working. When I google this it provides me with Access results, and I found someone saying to reference them like this:

Forms!FormName!ControlName

But what's the control name? And do I need to use the .Value code to get its value?

Who woulda thought referencing a form control would be so complicated!

I would really appreciate some help with this if anyone knows how to do it. Thanks a lot for the looks.

Hank

 

[MickG]

Try:-

[COLOR="Navy"]Sub[/COLOR] MG20May31
[COLOR="Navy"]Dim[/COLOR] oVal
[COLOR="Navy"]With[/COLOR] ActiveSheet.Shapes("PeriodDropDown").OLEFormat.Object
oVal = .List(.ListIndex)
[COLOR="Navy"]End[/COLOR] With
MsgBox oVal
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[hallingh]

Thanks for the code Mick. When I ran it, it gave me an error saying "Invalid Procedure Call or Argument."

Not sure what the error is. Do I need to Dim oVal as anything? Like String? Just making a feeble attempt at figuring out what's wrong!

Thanks again for the code.

Hank

 

[hallingh]

Nevermind. I messed up, but it is working now. Thanks a ton for the code!

Hank

 

[MickG]

Hi, If you assign a macro to the (Forms) Drop Down Box, then Place the code below in The macro,( that will appear as, "DropDown1.change" (or similar)), then you can use the result from your selection Directly from the Drop Down.

Sub DropDown1_Change()
Dim oVal as string ' Possibly (depedent on data)
With ActiveSheet.Shapes(Application.caller).OLEFormat.Object
oVal = .List(.ListIndex)
End With
MsgBox oVal

Regards Mick