Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Rowsource for sheet combobox (2003)

    This is one of those stupid questions to which I feel I should know the answer (or be able to find it easily). I have a combobox control on a worksheet (NOT a userform) and I'd like to set the rowsource for it on the fly. Looking at the control's properties, it doesn't have a rowsource as such but an "Input Range". I know that Excel VBA sees the control as a shape, so I can refer to it from the Shapes collection. What I can't figure out is how to set the "input range" for it from VBA. What's the secret?
    Waggers
    If at first you do succeed, you've probably missed something.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Rowsource for sheet combobox (2003)

    Is this a combo box from the Forms toolbar or from the Control Toolbox?

  3. #3
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rowsource for sheet combobox (2003)

    <img src=/S/doh.gif border=0 alt=doh width=15 height=15> The forms toolbar. There was me thinking they were the same! I'll use the one from the controls box instead.

    Thanks Hans, I should be ok now <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Rowsource for sheet combobox (2003)

    OK.

    Just for the record, mostly for others reading this thread: you can set the input range of a combo box from the Forms toolbar in VBA as follows:

    ActiveSheet.Shapes("Drop Down 1").ControlFormat.ListFillRange = "E1:E10"

    (Substitute the correct name and range)

    For a combo box from the Control Toolbox, you can use either

    ActiveSheet.Shapes("ComboBox1").OLEFormat.Object.L istFillRange = "E1:E10"

    or

    ActiveSheet.ComboBox1.ListFillRange = "E1:E10"

    (Again, substitute the correct name and range)

  5. #5
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rowsource for sheet combobox (2003)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> Thanks again Hans - much better than a loop full of .AddItem commands! <img src=/S/hiding.gif border=0 alt=hiding width=70 height=24> <img src=/S/innocent.gif border=0 alt=innocent width=20 height=20>
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

Posting Permissions

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