Populate cell with value from dynamically generated table on userform [SOLVED]

[dronka]

I'd like to do the following: I'm trying to create a tool to assign staff to various roles in an ongoing project. Each staff member has data associated with him/her (e.g. skills, years experience) that need to be taken into account when an assignment is made. Sheet 1 is where I want to enter the assignments. Sheet 2 contains the database with the staff roster and associated information.


Ideally, here's what I'd like to be able to do:

- Click on a cell where a staff name needs to be entered on sheet 1.

- A userform pops up with a complete staff roster and associated data for each staff.

- When I click on a staff name in the roster, that peson's name (not the associated data) is automatically entered into the cell on sheet 1.

- The userform automagically disappears.


I know how to create a dynamic dropdown list on a userform. But a dropdown list will only display one value (the name). I don't know how to display a table of information with the ability to then select one value from that table (the name), and for that value to then be entered into the cell on sheet 1.


Can it be done? If so, how? Thanks!

 

[NARAYANK991]

Hi dronka ,


If you mean a listbox , then you can display several columns of data in a listbox , by using the ColumnCount property ( making it greater than 1 ) , and the RowSource property ( setting it to the range containing data e.g. A4:G15


You can use the BoundColumn property to move the relevant data from the listbox selection to a worksheet cell.


Narayan

 

[dronka]

Thank you, Narayan. I didn't realize that about the listbox. I'll try it out and post my results.

 

[dronka]

Narayan - What code do I put into the RowSource property to get the data from, say, columns A:F on Sheet 2?

 

[SirJB7]

Hi, dronka!


Try with:

Sheet2!Axxx:Fyyy

where Sheet2 is the worksheet name (remember embracing it between apostrophes if contains spaces), xxx is the first row of the range, and yyy is the last row of the range.


Don't use references to entire columns unless you want a bunch of empty entries at the end of the data in the list box control.


Regards!

 

[dronka]

Great! I've been able to create a userform that displays multiple rows and columns of data from Sheet 1.


Now I'm wondering how to select one of the rows in the user form and transfer a data element in that row into a cell on Sheet 2. Narayan mentioned the BoundColumn property. I did some searches on that and found a lot of complex code. Is there an "easy" way to do this?

 

[NARAYANK991]

Hi ,


Let us assume the following have been set in the ListBox properties page :


1. RowSource : Sheet1!A2:C6


2. ColumnCount : 3


3. ControlSource : K1


4. BoundColumn : 2


The ListBox will now display 5 rows and 3 columns of data ; suppose you select the third row , then the ControlSource , which is cell K1 on the worksheet , will display the contents of cell B4 , since B4 is the second column in the displayed range ( BoundColumn = 2 ) , and 4 is the third row in the displayed range.


Narayan

 

[dronka]

Wow, that is too cool! So, all of that works great. Now I'm wondering if the control source can be whichever cell was selected before I pulled up the userform.

 

[NARAYANK991]

Hi ,


Sure. Do the following :

[pre]

Private Sub ListBox1_Click()
Unload Me
End Sub
Put the above code in the Userform section.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
UserForm1.ListBox1.ControlSource = Target.Address(0, 0, xlA1)
UserForm1.Show
End Sub

[/pre]
Put the above code in the Sheet section.


Now , when ever you click on a cell , the Userform will pop up ; when you select any row in the form , the BoundColumn property will decide which column value is to be used ; since the ListBox ControlSource is being set to the Activecell , the value will be put in that.


The ListBox1_Click procedure ensures that when you click , the Userform will disappear.


The only problem with this is that you cannot click on a cell which is already filled with data ; you can only click on an empty cell !


Narayan

 

[dronka]

Narayan - Thank you so much for this code. I was able to get the selection to fill in the cell that I clicked. This is WAY cool. Thank you!


David