PDA

View Full Version : Passing listbox values to cells



Skidz
07-06-2008, 05:23 PM
Hi !

I'm new here and to writing VBA, so please bear with me :)

I want to do the following:

1) When a user clicks on a single cell in column C or D of a sheet, a userform opens.

2)The user is presented with a listbox. The items in the listbox come from one of the columns in a 3-column Excel database on a sheet in the same workbook.

3) Once the user makes his/her choice and clicks on Ok, the data from the corresponding database entry is pasted to the cells in columns C, D and E on the row where the user initially clicked to envoke the userform.

4) I also want the Userform to allow for the addition of entries into the database.

5) The sheet containing the Excel database is hidden.

Now, I've figured out how to envoke the userform when the user clicks on certain columns, by using Worksheet_SelectionChange(ByVal Target As Range) and Application.Intersect(Target, Range(LastACRow)), so number one is solved, and I've figured out how to populate my listbox from the database using Userform.ListboxSelect.AddItem.

As I mentioned above, my knowledge of VBA is limited, so I'm having trouble with the following:

How do I get the data from the database entry corresponding to the listbox choice and paste it in the cells on the currently selected row of my sheet ? In trying to achieve this, I've noticed that if I define a variable (for example using ActiveCell.Row) in my worksheet sub, the values aren't available to the Userform for some reason.

If I define another userform to collect new data entries for my Excel database, how do I insert these new values into the database ?

Sorry for being so long-winded... :dunno