Prgrammatically added OLE Listbox, now cannot select from it

Discussion in 'Excel Programming' started by happyheth, May 5, 2014.

  1. happyheth

    happyheth Guest

    Hi,

    I have a macro that creates an OLEobject list box and popualtes it. Once the macro ends, I cannot select from the listbox unless I switch into design mode and back out.

    I have a single activex button that runs this code.

    Private Sub CommandButton1_Click()
    Set lb1 = ActiveSheet.OLEObjects.Add(ClassType:="Forms.ListBox.1", _
    Link:=False, DisplayAsIcon:=False, Left:=10, Top:=currX, Width:=560, Height:=60)

    With lb1.Object
    .AddItem
    .ColumnCount = 4
    .ColumnWidths = "100;150;150;150"
    .List(0, 0) = " "
    .List(0, 1) = "Database"
    .List(0, 2) = "Server"
    .List(0, 3) = "Version"
    End With
    End Sub

    Once complete, I what to select the row displayed but cannot. Help?
     
    happyheth, May 5, 2014
    #1
    1. Advertisements

  2. happyheth

    GS Guest

    > Hi,
    >
    > I have a macro that creates an OLEobject list box and popualtes it.
    > Once the macro ends, I cannot select from the listbox unless I switch
    > into design mode and back out.
    >
    > I have a single activex button that runs this code.
    >
    > Private Sub CommandButton1_Click()
    > Set lb1 =
    > ActiveSheet.OLEObjects.Add(ClassType:="Forms.ListBox.1", _
    > Link:=False, DisplayAsIcon:=False, Left:=10, Top:=currX,
    > Width:=560, Height:=60)
    >
    > With lb1.Object
    > .AddItem
    > .ColumnCount = 4
    > .ColumnWidths = "100;150;150;150"
    > .List(0, 0) = " "
    > .List(0, 1) = "Database"
    > .List(0, 2) = "Server"
    > .List(0, 3) = "Version"
    > End With
    > End Sub
    >
    > Once complete, I what to select the row displayed but cannot. Help?


    If you mean you want to select 1 of the subitems in the row.., you
    can't! You can only select the 1st column and doing so should highlight
    the entire row. Change you code so you have access to each item in its
    own row...

    With lb1.Object
    .AddItem: .AddItem "Database": .AddItem "Server": .AddItem
    "Version"
    End With

    ...so you get...

    blank (row1)
    Database (row2)
    Server (row3)
    Version (row4)

    OR
    use a ComboBox (dropdown)

    OR
    use a DataValidation list!

    --
    Garry

    Free usenet access at http://www.eternal-september.org
    Classic VB Users Regroup!
    comp.lang.basic.visual.misc
    microsoft.public.vb.general.discussion
     
    GS, May 5, 2014
    #2
    1. Advertisements

  3. happyheth

    happyheth Guest

    No that's not what I mean.

    When the script exits I cannot click on any part of the listbox unless I click on 'design mode' twice. I can see a fat cross when I hover over the listbox, and if I click the cursor temporarily turns to an arrowed cross. But it doesn't select the rows, instead it seems to be in another mode altogether.
     
    happyheth, May 6, 2014
    #3
  4. happyheth

    Peter T Guest

    "happyheth" <> wrote in message
    > Hi,
    >
    > I have a macro that creates an OLEobject list box and popualtes it. Once
    > the macro ends, I cannot select from the listbox unless I switch into
    > design mode and back out.
    >
    > I have a single activex button that runs this code.
    >
    > Private Sub CommandButton1_Click()
    > Set lb1 = ActiveSheet.OLEObjects.Add(ClassType:="Forms.ListBox.1", _
    > Link:=False, DisplayAsIcon:=False, Left:=10, Top:=currX,
    > Width:=560, Height:=60)
    >
    > With lb1.Object
    > .AddItem
    > .ColumnCount = 4
    > .ColumnWidths = "100;150;150;150"
    > .List(0, 0) = " "
    > .List(0, 1) = "Database"
    > .List(0, 2) = "Server"
    > .List(0, 3) = "Version"
    > End With
    > End Sub
    >
    > Once complete, I what to select the row displayed but cannot. Help?


    Try toggling design mode with code, eg

    Dim cb As CommandBarButton
    Set cb = Application.VBE.CommandBars.FindControl(ID:=212)
    cb.Execute
    cb.Execute

    Can also toggle the ribbon button with
    CommandBars.ExecuteMso "DesignMode"
    though it can be a bit fiddly to avoid the code terminating on that
    particular call

    Regards,
    Peter T
     
    Peter T, May 6, 2014
    #4
  5. happyheth

    GS Guest

    > No that's not what I mean.
    >
    > When the script exits I cannot click on any part of the listbox
    > unless I click on 'design mode' twice. I can see a fat cross when I
    > hover over the listbox, and if I click the cursor temporarily turns
    > to an arrowed cross. But it doesn't select the rows, instead it seems
    > to be in another mode altogether.


    You need to enter design mode to create it, then leave design mode to
    use it. That precludes 2 clicks are required!

    --
    Garry

    Free usenet access at http://www.eternal-september.org
    Classic VB Users Regroup!
    comp.lang.basic.visual.misc
    microsoft.public.vb.general.discussion
     
    GS, May 6, 2014
    #5
  6. happyheth

    happyheth Guest

    I've tried programmatically switch but it seems to end the script when you go into design mode.
     
    happyheth, May 7, 2014
    #6
  7. happyheth

    GS Guest

    > I've tried programmatically switch but it seems to end the script
    > when you go into design mode.


    That's correct! What is it, exactly, that you're trying to accomplish?

    --
    Garry

    Free usenet access at http://www.eternal-september.org
    Classic VB Users Regroup!
    comp.lang.basic.visual.misc
    microsoft.public.vb.general.discussion
     
    GS, May 7, 2014
    #7
  8. happyheth

    happyheth Guest

    Its pretty simple. I'm trying to produce something I can give to an inexperienced user and they can click. I'd have used proper VB but my company wontbuy it.

    I'm create a worksheet, adding a listbox, populating the listbox (for oracle). Then the user can select one or more entries from the listbox and they then perform other queries. But as I've said, when I finish running the first script, the user cannot immediate select the entries from the listbox. No very user friendly. The code I showed earlier demonstrates this. I'm sureI use a textbox or not a oleobject etc etc but I want to do it this way, and I dont think its too much to ask. I'm sure this must be something in my settings within excel that prevents an ole listbox from selecting after itsbeen created programmatically.
     
    happyheth, May 8, 2014
    #8
  9. happyheth

    Peter T Guest

    "happyheth" <> wrote in message
    Its pretty simple. I'm trying to produce something I can give to an
    inexperienced user and they can click. I'd have used proper VB but my
    company wont buy it.

    I'm create a worksheet, adding a listbox, populating the listbox (for
    oracle). Then the user can select one or more entries from the listbox and
    they then perform other queries. But as I've said, when I finish running the
    first script, the user cannot immediate select the entries from the listbox.
    No very user friendly. The code I showed earlier demonstrates this. I'm sure
    I use a textbox or not a oleobject etc etc but I want to do it this way, and
    I dont think its too much to ask. I'm sure this must be something in my
    settings within excel that prevents an ole listbox from selecting after its
    been created programmatically.
    ===========================

    Did you try what I suggested?

    Peter T
     
    Peter T, May 8, 2014
    #9
  10. happyheth

    happyheth Guest

    Peter T, your the man. Great Suggestion and it does work. The first bit. I dont suppose there is an explanation but I'm happy. Thanks.
     
    happyheth, May 8, 2014
    #10
    1. Advertisements

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. ARB
    Replies:
    0
    Views:
    294
  2. travis

    Made a button, added code, now what?

    travis, Feb 3, 2006, in forum: Excel Programming
    Replies:
    2
    Views:
    77
    travis
    Feb 3, 2006
  3. KR
    Replies:
    4
    Views:
    250
  4. Replies:
    1
    Views:
    190
    Wendell A. Clark
    May 17, 2006
  5. Andreww
    Replies:
    0
    Views:
    108
    Andreww
    May 31, 2007
  6. GusEvans
    Replies:
    3
    Views:
    200
    Incidental
    Jul 19, 2007
  7. Sriram
    Replies:
    2
    Views:
    153
    Sriram
    Oct 30, 2007
  8. Cannot select items in listbox

    , Mar 25, 2008, in forum: Excel Programming
    Replies:
    0
    Views:
    95
Loading...