Results 1 to 2 of 2

Thread: ListBox or ????

  1. #1
    Join Date
    Jun 2004

    Unanswered: ListBox or ????

    I've done considerable VBA coding but mostly processing downloaded data in Excel files. (I'm not officially an IT person, just doing this as a matter of survival) I've hit the wall on trying to give the user a choice of items that will allow a program to use different sets of rates to compute a cost for each transaction in a file. It seems like this would be a list box but I can't figure out how to get a list box to even appear. I'm not trying to embed a list box in a spreadsheet, I just want it to appear for the user to make a selection which will assign to a variable a value associated with the selection. Similar to what happens with an input box but in this case from a predetermined, drop-down list.

  2. #2
    Join Date
    Apr 2004
    Derbyshire, UK
    Provided Answers: 1


    Hi clarkn

    I do not know how familiar you are with VBA, VB6 or Access Forms etc.
    but to use a list box you will need to add a UserForm tor the workbook in the VB window and then add a Listbox to the form from the toolbox (also in the VB window). The default names are Userform1 and ListBox1, but these can be changed using the properties (all the properties are explained in the VB help).

    In the code module you can write code to open the form (from a button on the spreadsheet or some other code) and declare a Public variable that can remember info from the form, such as :-

    Option Explicit
        Public ListValue As String
    Sub ShowForm()
        Cells(1, 1) = ListValue
    End Sub

    To use the list box you have to populate the list box with data (in the form Activate event usualy) and then remember which item as been selected when the UserForm is hidden or closed. An example of code in the UserForm module as follows:-

    Option Explicit
    Private Sub CommandButton1_Click()
    End Sub
    Private Sub ListBox1_AfterUpdate()
        ListValue = ListBox1.Value
    End Sub
    Private Sub UserForm_Activate()
        ListBox1.AddItem "Item 1"
        ListBox1.AddItem "Item 2"
        ListBox1.AddItem "Item 3"
        ListBox1.AddItem "Item 4"
        ListBox1.AddItem "Item 5"
        ListBox1.AddItem "etc."
    End Sub
    This code is for a single column only (using multiple columns and multi-select from the list is a little more complicated, but is documented in the VB help if you are interested).

    I do not know if this helps but a book on VBA would help you no end).

    If you have any specific problems then I may be able to help but it is dificult if you have never used firms in VBA,VB or access.

    Best of luck


Posting Permissions

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