Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Strangeness in Excel 2000 VBA

    I've got a little routine that populates comboboxes on a userform from a named range.

    Dim cboBox as combobox
    Dim rngRange as Range

    Set cboBox = combobox1
    Set rngRange = Range("Stuff")
    Call LoadComboBox(rngRange, cboBox)

    Anyway the above works just fine. When I try to do it using ListBoxes

    Dim lstBox as ListBox
    Dim rngRange as Range

    Set lstBox = ListBox1

    I get a type mismatch error. I can get it to work Dimming lstBox as an object or control. Why does it work using comboxes but not listboxes? As I have a work-around, this isn't urgent. It's just strange. Anyone have any ideas? <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    Oh, I'm using Excel 2000 SP1

  2. #2
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Strangeness in Excel 2000 VBA

    Just a crazy thought...

    What happens if you

    Dim lstBox as Userform.Listbox
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  3. #3
    Rob Bruce
    Guest

    Re: Strangeness in Excel 2000 VBA

    Since Excel 97 and the advent of MSForms userforms, Excel has had two different ListBox objects. To see this, create an empty workbook, go into the VB Editor, add a userform, and then open the object browser. Right-click on the "Members" pane and select "Show Hidden Members" from the pop-up menu. Now ensure that "<all libraries>" is selected in the top-leftmost drop-down list. Scroll down the list of Classes and you will find two ListBox objects. One is hidden (a legacy of Excel 5/95 forms controls) and one is the ActiveX control supplied by the MSForms library you are interested in. Unfortunately, despite being hidden, the old, legacy, control has priority in Excel's object hierarchy. The secret is in the object declaration: Include the ListBox object's parent library in the Dim statement and VBA will know exactly which object you want.
    Dim lstBox as MSForms.ListBox
    ...should do the job.

    Rob

  4. #4
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Strangeness in Excel 2000 VBA

    I'm not worthy <img src=/S/exclamation.gif border=0 alt=exclamation width=15 height=15> I'm not worthy <img src=/S/exclamation.gif border=0 alt=exclamation width=15 height=15> <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

    That did the trick! MSForms.ListBox!

    Thanks Rob & Kevin(Hey, you were close.)

Posting Permissions

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