Listbox Populating

  • I have acquired the following code to scan through a workbook to list all
    the sheetnames and this works fine.


    For SHT = 1 To ActiveWorkbook.Worksheets.Count
    MsgBox Worksheets(SHT).Name
    Next SHT


    I am a relative beginner to VBA and am having trouble trying to get this information into a listbox so that a particular sheetname can be selected.


    Could some kind programmer out there provide me with an example XLS
    containing the code to perform this operation.


    I can drag and create a listbox on an excel sheet, but I cannot go any further
    in naming it or adding data to it.


    I can also create a listbox on a userform in VBE, but again I cannot get it to
    display with added items in it.


    Your help will be greatly appreciated, as the frustration that this apparently
    simple task is causing, is not doing my blood pressure any favours !


    Cheers,


    Kevin

  • Re: Listbox Populating


    Hi Kevin,


    Add the following code to a userform code module.
    The userform should have a listbox on it.


    Code
    Private Sub UserForm_Initialize()
        
        Dim SHT As Integer
        
        For SHT = 1 To ActiveWorkbook.Worksheets.Count
            ListBox1.AddItem Worksheets(SHT).Name
        Next SHT
    End Sub

    [h4]Cheers
    Andy
    [/h4]

  • Re: Listbox Populating


    Thanks a million Andy that code works a treat.


    I am experienced in running subprocedures, but can you tell me the simplest way or ways to run a userform ?


    At present, I have a macro subprocedure that simply says UserForm1.Show


    After I have selected an item from the pop-up, the pop-up disappears as you would want it to, BUT the original userform remains visible.


    What is the best way to get the userform to vanish ?


    Thanks again for your time and expertise.


    Cheers,


    Kevin

  • Re: Listbox Populating


    Hi Kevin,


    I'm not sure what popup you are refereing to, maybe something in your project?


    Anyway you can use Unload , normally run by pressing a button on the userform.


    Code
    Private Sub CommandButton1_Click()
        Unload Me
    End Sub

    [h4]Cheers
    Andy
    [/h4]

  • Re: Listbox Populating


    Thanks once again Andy for coming to my rescue.


    I realised later that using the term pop-up would not be very clear.


    The pop-up was simply the result of a MsgBox of the chosen sheet name !


    Sincere apologies if I wasted any of your time on that point.


    I have slotted in your code to unload the userform and everything now works
    perfectly.


    It sure is a wonderful world where absolute strangers donate their time unselfishly to help other absolute strangers !


    My blood pressure has now returned to normal !


    I cannot thank you enough.


    Cheers,


    Kevin

  • Re: Listbox Populating


    Hi Andy,


    This seems to be a good tool getting displayed all the worksheets in a work book.


    While I can do upto the displaying stage, could you please guide as to how when I select a particular sheet that particular sheet gets activated.


    Please help..



    thanx
    Pradeep

  • Re: Listbox Populating


    Hi Pradeep,


    This is the code with the help from Andy, which shows both procedures, that I have on my userform - I believe, that you were interested in the 2nd procedure in particular.


    In this example, the selected sheet name is pasted into an excel sheet, but you can do what you want with it.


    ' - - - - - - - - - - - - - - - - - - - - - - - - - - - - -


    Private Sub UserForm_Initialize()


    For SHT = 1 To ActiveWorkbook.Worksheets.Count
    ListBox1.AddItem Worksheets(SHT).Name
    Next SHT


    ' If Left(Sheets(SHT).Name, 3) = "CMM" Then ListBox1.AddItem Worksheets(SHT).Name ' Restrict selection


    End Sub




    Private Sub ListBox1_Click()


    SHTname = ListBox1.Value
    Sheets("InputVals").Cells(2, 6) = SHTname
    Unload Me


    End Sub


    ' - - - - - - - - - - - - - - - - - - - - - - - - - - - - -


    I have also left in a line of code as a comment that can replace the Listbox1 line if you wish to restrict what sheets are selected - in this case, it would
    only select sheets that began with the string "CMM" - you might find this helpful.


    Cheers,


    Kevin

  • Re: Listbox Populating


    Hi Kevin,


    thanx for your reply.


    It seems I have not made my point clear in my post.


    What I wanted is:
    When I select a sheet name in the list box that particular sheet should get activated.


    This utility will be the same as the mouse "right click" option on the arrow marks to the left side of sheet tab. And the only difference here would be ere I can have the list displayed with a key board short cut and I need not use mouse(which makes the job faster).


    thanx
    Pradeep

  • Re: Listbox Populating


    Hi again Pradeep,


    To fully answer your queston, replace the line before the
    Unload Me line with:


    Sheets(SHTname).Select


    You should find yourself in the selected sheet.


    Cheers,


    Kevin

  • Re: Listbox Populating


    Hi Kevin,


    Thanx for the solution. It worked.


    A small problem..is it possible to use key board movement as the selection mode instead of doing mouse click.


    And one more point here is that i got lot more hidden sheets which appear early in the list by the time key stroke goes there it throws up some error message there...any way to get over this..


    thanx
    Pradeep

  • Re: Listbox Populating


    Hi Pradeep,

    I am sorry to say that I cannot answer your last two questions, as
    I am a relative beginner in this area.
    The information that I got regards Listbox processing was only acquired yesterday with Andy Pope's help.
    Hopefully, a more experienced programmer will come to your aid.
    If you do get any answers, I would be most grateful if you could
    pass it on to me.


    Cheers,


    Kevin

  • Re: Listbox Populating


    Hi Guys,


    The follow will only add visible sheets to the listbox.
    Double-clicking or pressing enter will activate the sheet and then close the userform.

    [h4]Cheers
    Andy
    [/h4]

  • Re: Listbox Populating


    Tanks once again Andy for a very comprehensive answer.


    I am sure that Pradeep will be very happy with your response.


    Cheers,


    Kevin

  • Re: Listbox Populating


    Hi Andy,


    Apologies for the delay in replying, infact I was awaiting e-mail notification.


    Regarding your comprehensive solution...what do I say...U are a GURU..(I wish i would become one)......It just works the way I wanted..!!! :rock:


    And I assigned it to a key board short cut(by way of a macro in my personal workbook(hidden)) and have the list displayed with this key board short cut wherever I want......Delighted...


    And this should be useful to many other who are working on workbooks with umpteen sheets....


    Keven, thanx to u too..


    Thanx a ton!
    Pradeep

  • Re: Listbox Populating


    Hi Andy,


    While the code you suggested works great..is it possible to use the 'ESC' key to unload the UserForm, instead of Close button.


    Should be Some thing like this...


    [vba]
    Application.EnableCancelKey = xlEnabled
    [/vba]


    thanx

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!