Results 1 to 15 of 15

Thread: listboxes

  1. #1
    david_grimmer
    Guest

    listboxes

    Hi all, I

  2. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: listboxes

    Which version of Excel are you using? PC or Mac? Windows version?

    I add some code that uses some properties and methods of the listbox control. In Excel 97 or 2000, make a userform and add a listbox and commandbutton control. Then you can use code like this:

    Private Sub CommandButton1_Click()
    Dim i As Integer
    For i = 1 To ListBox1.ListCount
    If ListBox1.Selected(i) = True Then
    MsgBox "You selected " & ListBox1.List(i)
    End If
    Next i
    End Sub

    Private Sub UserForm_Initialize()
    ListBox1.AddItem "Test 1"
    ListBox1.AddItem "Test 2"
    ListBox1.AddItem "Test 3"
    ListBox1.AddItem "test 4"
    End Sub

  3. #3
    david_grimmer
    Guest

    Re: listboxes

    sorry about the lack of information, as i said , i'm new to this
    I'm running excel 2000 under windows 98

    I'm trying out your code, will let you know

    thanks in advance

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: listboxes

    First, I don't find a collection named DialogSheets. Did you by chance mean Dialogs? That is the collection of Excel dialogs.

    Second, did you really create a dialog, or did you create a userform? I know that it is possible to create a dialog and add it to the dialogs collection, but I have never done that and don't know how. As far as I know, the only useful thing that you can do with the Dialogs collection is to use it with the .Show method to show a dialog from the collection (usually one of the Excel built in dialogs). Your example indicates that what you have is a userform, not a dialog box.
    Legare Coleman

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: listboxes

    Are these DialogSheets or Dialog modules not an Excel 5 thing?

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: listboxes

    I found the following concerning dialog sheets in John Walkenbach's book "Excel 2000 Power Programming with VBA":
    "Userforms replaced the clunky old dialog sheets and gave you much more control over your custom dialog boxes. However, for compatibility purposes, Excel 97 and Excel 2000 still support Excel 5/95 dialog sheets."

  7. #7
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: listboxes

    Hi,

    This might not help- but you can make any routines you want hidden private, and they won't show up in the xlDialogRun list.

    eg- "Private Sub mySub"

    Of course, then they won't show for you either
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  8. #8
    david_grimmer
    Guest

    Re: listboxes

    Thanks for all your replies, just to clear things up, yes i was refering to userforms, sorry, the dialog thing is from when i used to use excel 95/97.
    Hans, your code works fine for displaying a message box, thanks, but how do i get it to run a named macro

    Private Sub CommandButton1_Click()
    Dim i As Integer
    For i = 1 To ListBox1.ListCount
    If ListBox1.Selected(i) = True Then

    ***********
    "what do i change this to"
    MsgBox "You selected " & ListBox1.List(i)

    to get it to run one of the macros
    *****************
    End If
    Next i
    End Sub

    Private Sub UserForm_Initialize()
    ListBox1.AddItem "macornameA"
    ListBox1.AddItem "macronameB"
    ListBox1.AddItem "macronameC"
    ListBox1.AddItem "test 4"
    End Sub

    Thanks

    david

  9. #9
    david_grimmer
    Guest

    Re: listboxes

    Thanks for all your replies, just to clear things up, yes i was refering to userforms, sorry, the dialog thing is from when i used to use excel 95/97.
    Hans, your code works fine for displaying a message box, thanks, but how do i get it to run a named macro

    Private Sub CommandButton1_Click()
    Dim i As Integer
    For i = 1 To ListBox1.ListCount
    If ListBox1.Selected(i) = True Then

    ***********
    "what do i change this to"
    MsgBox "You selected " & ListBox1.List(i)

    to get it to run one of the macros
    *****************
    End If
    Next i
    End Sub

    Private Sub UserForm_Initialize()
    ListBox1.AddItem "macornameA"
    ListBox1.AddItem "macronameB"
    ListBox1.AddItem "macronameC"
    ListBox1.AddItem "test 4"
    End Sub

    Thanks

  10. #10
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: listboxes

    Via Tools >> Macro >> Macros, you already obtain a list of available macros from which you can easily start the macro of your choice. So, why are you reinventing the wheel? If, on the other hand, you want to make visible the userform with the listbox by running a macro, proceed as follows:

    Just add a module to your project and put in something like this:

    Sub Display_Form()
    UserForm1.Show
    End Sub

    The Display_Form subroutine will appear in the macro list box from where you can run it. Of course, there are several other possibilities to make your macro run (e.g. put a button on your spreadsheet and attach the macro to it).

  11. #11
    david_grimmer
    Guest

    Re: listboxes

    you misunderstand, I'm reinventing the wheel as you put it, because i'm writing a number of macros that others will be using, and i dont want then to see all of the macros in the file, only the ones that I need them to see.

    David

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: listboxes

    Hi David,
    You can change the msgbox line to:
    Application.Run Listbox1.List(i)
    to run the selected macro.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: listboxes

    Do you require only that your users select a Macro to Run, or do you require that they can edit the macro.

    If you just want give them access to certain macros for the purpose of running them you could create a custom toolbar with the macros in question or a UserForm with a Button to invoke each macro.

    Maybe I have misunderstood your dilemma

    Andrew C

  14. #14
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: listboxes

    I think Rory pointed you in the right direction. Sorry for the misunderstanding.

  15. #15
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: listboxes

    Just as an addendum to my last post, if you're dealing with a relatively small number of macros, I'd go with Andrew's suggestion of a toolbar - it's probably a little more user-friendly.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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