Annoying code error in getting a DropDown value (VBA for Excel)

Topic

New Reply

Sheesh!! All I wanna do is….

In a project long long ago I needed to pull the selected value from a drop down on a User form and found the following code to work:

AInt = DialogSheets(“New Assignment”).DropDowns(1).Text

which loads the variable AInt with whatever was selected in the Drop Down on the user form. Now I’m trying to do the same thing but I keep getting the good old ‘out of range’ error when referencing the dialog sheet and drop down in code:

Sub ReturnSelection()
RegionalSelection = DialogSheets(“MainForm”).DropDowns(4).Text
MsgBox “You Chose ” & RegionalSelection
End Sub

I tried any number of numbers for the danged DropDown on the user form, and it is listed as Drop Down 4 in the Name box and also in the tab field box, so you’d think it’s actually what it claims to be…VBA can’t find it.

One small difference in the two, this particular drop-down box references a named range that is generated in code as I need to allow for additions/deletions of the options displayed.

I need to load a public variable with the text picked in the drop-down in order to set a lot of SQL strings with whatever choice is made in the dialog, so it is *crucial* that I get the data from the DropDown. I know some people will advise a goofy Select Case.. logic for the cell link and & don’t want to do that as I’ll have to edit it if and when the values change. It seems dirt easy to just grab the selected value but Mr VBA is not coorperating in one project and is happy in another, so what gives??

TIA

Reply | Quote

Replies

I don’t know what a DialogSheet is, but I’ll assume it has a Controls collection. Can you use .Controls(“DropDown4”) as your index?

Reply | Quote

Dropdowns(4) means the fourth dropdown. You should use
Dropdowns(“Drop Down 4”)

Reply | Quote

(Edited by steve_skelton13 on 13-Feb-04 17:13. )

FIXED – instead of trying to grab the dropdown text directly I set up an index on the data page and load the variable from the returned value named range.

that sort of works, but VBA now says it can’t access the Text property of the DropDown class. If I use .Value I get the index value of the selection, not the selection itself, which is not what I want to do. If I use RegionalSelection = DialogSheets(“MainForm”).DropDowns(1).Value instead it also gives me the index value of the selection in the form.

The problem isn’t how to reference the DropDown control but how to get the actual selection text specified in the control loaded to a variable. If I re-assign the values in the dropdown control to a series of data (rather than a named range) the problem persists, so apparently there’s no issue with using a named range for the dropdown list source values.

What’s weird is I have done this in the past: in another project I have a complex Form and set variables in VBA according to the selected text in the controls, not according the the index number of the value, which is very handy from a user point of view (the user doesn’t even know he’s loading variables and the interface is familiar) and it’s useful in the VBA as well as I don’t even have to reference the cell link or the data source to load a variable according to the control’s selection. So you can see how handy doing this is.

Strange…

Reply | Quote

For the old listbox controls used on dialog sheets, you use something like:

With Dialogsheets(“Mysheet”).Listboxes(“List box 1”)
sSelected=.List(.Listindex)
End With

Reply | Quote

For the old listbox controls used on dialog sheets, you use something like:

With Dialogsheets(“Mysheet”).Listboxes(“List box 1”)
sSelected=.List(.Listindex)
End With

Reply | Quote

(Edited by steve_skelton13 on 13-Feb-04 17:13. )

FIXED – instead of trying to grab the dropdown text directly I set up an index on the data page and load the variable from the returned value named range.

that sort of works, but VBA now says it can’t access the Text property of the DropDown class. If I use .Value I get the index value of the selection, not the selection itself, which is not what I want to do. If I use RegionalSelection = DialogSheets(“MainForm”).DropDowns(1).Value instead it also gives me the index value of the selection in the form.

The problem isn’t how to reference the DropDown control but how to get the actual selection text specified in the control loaded to a variable. If I re-assign the values in the dropdown control to a series of data (rather than a named range) the problem persists, so apparently there’s no issue with using a named range for the dropdown list source values.

What’s weird is I have done this in the past: in another project I have a complex Form and set variables in VBA according to the selected text in the controls, not according the the index number of the value, which is very handy from a user point of view (the user doesn’t even know he’s loading variables and the interface is familiar) and it’s useful in the VBA as well as I don’t even have to reference the cell link or the data source to load a variable according to the control’s selection. So you can see how handy doing this is.

Strange…

Reply | Quote

Dropdowns(4) means the fourth dropdown. You should use
Dropdowns(“Drop Down 4”)

Reply | Quote