Results 1 to 4 of 4
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    accessing list box in Form Toolbar (Excel 2000)

    I have a list box on a worksheet that came from the Form tool bar (not the Controls/ActiveX tool bar). I need to write code that gets run when a selection is made in that list box. I have named the list box and did the 'assign macro' when I right-click the control.

    My problem now is I don't know how to identify the list box in code. If this was an ActiveX list box I'd just use the .OleObjects("lstBoxName") and be done, but I don't know how to identify the Form listbox. <img src=/S/sigh.gif border=0 alt=sigh width=15 height=15>

    I need to determine which item was selected and if one particular item was chosen I then do some formatting to another worksheet (show/hide certain columns and adjust formulas).

    <pre>Private Sub lstEquip_Click()
    Dim ob as OleObject

    Set ob = Thisworkbook.Worksheets("setup").OleObjects("lstEq uip").OleObjects

    ' this doesn't work of course

    end Sub</pre>


    Thnx, Deb

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

    Re: accessing list box in Form Toolbar (Excel 2000)

    I still happen to have XL5's VBA Help file, so...

    With ActiveSheet.ListBoxes("List Box 1")
    MsgBox .List(.ListIndex)
    End With
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: accessing list box in Form Toolbar (Excel 2000)

    Deb,

    A slight amendment to Jan Karel's code will allow you to identify the listbox that invoked the code :

    With ActiveSheet.ListBoxes(Application.Caller)
    MsgBox .List(.ListIndex)
    End With

    Andrew C

  4. #4
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: accessing list box in Form Toolbar (Excel 2000)

    Great, that worked! It seems weird that it's part of the hidden (and therefore not recommended for use) <img src=/S/ouch.gif border=0 alt=ouch width=15 height=15> part of the Help section. If that's the case they should of also hidden the Form tool bar.

    I usually use ActiveX tool bar (more functionality) but in this case, the person who wrote the formulas had already used lots of list boxes and check boxes and I didn't want to have to change everything to work with the ActiveX controls.

    Thnx, Deb <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

Posting Permissions

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