Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Sheesh!! All I wanna do is.... <img src=/S/drop.gif border=0 alt=drop width=23 height=23>

    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?? <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    TIA

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

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

    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?

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Dropdowns(4) means the fourth dropdown. You should use
    Dropdowns("Drop Down 4")
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    <P ID="edit" class=small>(Edited by steve_skelton13 on 13-Feb-04 17:13. )</P> <font color=448800>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.</font color=448800>

    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... <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15>

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •