ListBox or ????

clarkn · #1 (**permalink**) 06-10-04, 13:12

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.

MikeTheBike · #2 (**permalink**) 06-11-04, 08:11

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 :-

Code:

Option Explicit
    Public ListValue As String
Sub ShowForm()
    
    UserForm1.Show
    
    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:-

Code:

Option Explicit

Private Sub CommandButton1_Click()
    Me.Hide
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

MTB

Internet Services

Web Development

Digital Marketing

Consumer Tech