Page 1 of 2 12 LastLast
Results 1 to 10 of 14

VBA Change .List Range of Drop Down Box

This is a discussion on VBA Change .List Range of Drop Down Box within the Excel Questions forums, part of the Question Forums category; I have the following Code The first Part copies a horizontal range and transposes it in B510 C509 reads a ...

  1. #1
    Board Regular
    Join Date
    Dec 2002
    Location
    Chicago, IL
    Posts
    77

    Default

    I have the following Code
    The first Part copies a horizontal range and transposes it in B510

    C509 reads a cell that counts the total # of entries in the list.

    My difficulty comes at the end, when I am trying to select the list box and change the listfillrange for the content.

    The list box does get selected successfully, but the listfillrange is not modified.

    Also, I would like to know how to deselect the Drop Down Box after I have modified it.

    'Rebuild Skills List
    Sheets("AdminSkillsProfessions").Range("W1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets("AdminSkillsProfessions").Range("B510").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=True

    x = Sheets("AdminSkillsProfessions").Range("C509").Value

    ActiveWorkbook.Sheets("UserForm").Shapes("Drop Down 1").Select
    With Selection
    ListFillRange = "AdminSkillsProfessions!$B$510:$B$" & x
    End With



    [ This Message was edited by: ArthurReyes on 2002-12-13 14:28 ]

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339

    Default

    Are just missing a period in front of the ListFillRange bit?

  3. #3
    Board Regular
    Join Date
    Dec 2002
    Location
    Chicago, IL
    Posts
    77

    Default

    right you are, now I'm at least getting an error message:

    Object doesn't support this property or method

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339

    Default

    Ah, hang on- you would need to activate the worksheet first with your code, try this variation and amend to suit your worksheet names and ranges:-

    Sheets("Sheet3").Shapes("Drop Down 1").ControlFormat.ListFillRange = "Sheet2!$B$1:$B$3"

    Personally, I would stick to using controls from the toolbox menu, much easier to manipulate in code as they expose more properties.

  5. #5
    Board Regular
    Join Date
    Dec 2002
    Location
    Chicago, IL
    Posts
    77

    Default

    I have to select the stupid sheet first.

    Sheets("Attributes").Select

    ActiveSheet.Shapes("Drop Down 1").Select
    With Selection
    .ListFillRange = "AdminSkillsProfessions!$B$510:$B$" & x
    End With

    however, I don't know how to deselect the object when I am done

  6. #6
    Board Regular
    Join Date
    Dec 2002
    Location
    Chicago, IL
    Posts
    77

    Default

    I have no experience with the Toolbox, only forms. I need to work on that.

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339

    Default

    You won't need to select the sheet if you use the piece of code above. In fact it's quite rare that you will have to select or activate anything using VBA.

  8. #8
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    4,950

    Default

    Hi Mudface,

    It is true,you don't need to select the sheet for the code to work. However you MUST ACTIVATE the sheet if it not active.

    OK we have found a way around the problem trough trial and error which is great but we still don't know the logic behind this
    VBA code behaviour.

    So the question is -Why can we not set the ListFillRange property with the code below when the sheet on which the control is embeded is not active !!?:

    Sheet(1).shapes("DROP DOWN 1").ListFillRange="Sheet1!$A$1:$A$10")

    I just can't spot anything wrong with this code.

    By the way,this also applies to controls from the Control toolbox.

    Does anyone out there have an explaination as to why this happens?

    Thank you.

    Jaafar.

  9. #9
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209

    Default

    On 2002-12-14 23:43, rafaaj2000 wrote:
    Hi Mudface,

    It is true,you don't need to select the sheet for the code to work. However you MUST ACTIVATE the sheet if it not active.

    OK we have found a way around the problem trough trial and error which is great but we still don't know the logic behind this
    VBA code behaviour.

    So the question is -Why can we not set the ListFillRange property with the code below when the sheet on which the control is embeded is not active !!?:

    Sheet(1).shapes("DROP DOWN 1").ListFillRange="Sheet1!$A$1:$A$10")

    I just can't spot anything wrong with this code.

    By the way,this also applies to controls from the Control toolbox.

    Does anyone out there have an explaination as to why this happens?

    Thank you.

    Jaafar.
    1st you don't need to Activate the sheet
    to change an Objects property.

    2nd The code you supplied will not change it
    as the syntax is wrong
    The code you gave has an extra ")" but even with this removed the object does not support that syntax

    Here is what worked for me Xl2000 on any sheet...


    Sub Tester()
    Sheets(2).Shapes("DROP DOWN 1").ControlFormat.ListFillRange = "Sheet1!$A$1:$A$10"
    End Sub


    You must use the Shapes ControlFormat property to change the property of a control
    from the Forms commandbar.




    _________________
    Kind Regards,
    Ivan F Moala From the City of Sails


    [ This Message was edited by: Ivan F Moala on 2002-12-15 00:22 ]

  10. #10
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    4,950

    Default

    Thanks Ivan.
    The code does work with the Controlformat property but the object I was experimenting with on my computer was actually a ComboBox from the Control ToolBox not a drop down from the forms box.

    Do you know how to carry out the same task on a combobox . I tried the Controlformat but it does not support this property ?


    Thank you.
    Jaafar.



Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com