Excel 2007 : Sending Listbox selections to a worksheet cell

[teachmedave]

I'm trying to create a form that my Program Administrators (Department Deans) might be able to use for evaluating and observing their faculty. I've created a sheet that has check boxes for most of the selections, but at the end of each section there is a comments area that I've created a listbox that contains some choices. I thought that this would be simple, but has proven to more involved that I thought.

All that I would like to do is to have the selections that are made from the list box populate into the comments cell so that when the sheet is printed the comments can be shown.

Apparently, I might be in need of a simple VB script to put into this. I would have thought that this would have been one of the primary functions of a listbox in the first place to be able to show the selections to be printed. Does anyone have any ideas or something that could help?

[Simon Lloyd]

When you say "Form" do you mean you are using a userform or you have
made a worksheet to act as a form? if the latter where did you add the controls from? was it the controls toolbox or the forms toolbox?

If you are using the controls toolbox on a worksheet you use the Linked Cell property so when you make a selection in the listbox the linked cell displays the selection.

[teachmedave]

Sorry for being so vague. The form is just a paper that I'm making for our department heads to be able to fill out and hand in. I'm trying to make it so they don't have to type anything, just click and print. In the worksheet, I have just used check boxes to indicate a level of proficiency. Then at the end of each section there is a comments area in which I have made a listbox for them to select a comment.

I tried to use the cell link option, but it just places a 0 in the cell. I'm not sure what to do with it from there.

Eventually after I figure this out, I want to find a way to be able to make it so we can use it on a PDA, but I need to cross this hurdle first.

Thank you for being so prompt with your reply. ^_^

[Simon Lloyd]

A couple of ways:

Please Login or Register  to view this content.

where A1 would be your comments box, the above sub goes in the worksheet code module, or after the final listbox has a selection made it can go in the comments cell like this:

Please Login or Register  to view this content.

[teachmedave]

Thank you so much for your reply. I'm afraid that I'm not quite able to get it to work. I'm a real novice at this. I copied the code, went into the work sheet, right-clicked one of the listboxes, clicked on the developer tab, went to the controls area and clicked on the "View Code" button that brought up the VB window, I pasted the first code into the window that it brought up which it designated in the top right-hand corner: Listbox312_Change.

I'm not sure where I should put this code in, I guess. I thought that all I might need to do is replace the listbox number from your code with my numbers that I'm using and change the cell address in your code (A1) with mine (B13). Do I need to select the listbox or does this code work for them all?

I tried it by trying to enter it in the "Sheet1" are and then again in the "Modules" area. Do I need to create a new module?

Can this be done for each individual ListBox so that each box's selections can go in seperate comment areas?

[Simon Lloyd]

Firstly you didn't state you are using xl2007! secondly the way to access the sheet code is by right clicking the tab choose view code and then paste whichever code you are using then change the listbox names for the ones you are using!


Moved to xl2007 help forum

[teachmedave]

Thank you, my apologies for not stating that I'm using 2007. I will try this immediately. I do apreciate your responses and help. Can this be done for each individual listbox so each listbox's selections can go into their own comment areas? I can work with it either way, I was just wondering. ^_^

[Simon Lloyd]

Please Login or Register  to view this content.

Please Login or Register  to view this content.

....etc.

[teachmedave]

Thank you so much for your patience. I pasted the code in the worksheet code and it come up with an error that stated that it couldn't run it because either the macros were not enabled or the method or something was non-existant with the name of the sheet and listbox number. However, the listbox number it came up with was 343 and I changed it to 1. I then went and tried to change the code to 343, but it come up with a compile error.

After that I wondered if I needed to record a macro in which I did, I pasted the code in that area and that didn't seem to work either. I then tried to erase all the code start from scratch, but it still seems to remember.

It seems that the first line that it has a problem with is Private Sub ListBox1_Click(), which it highlights in yellow. Is there some syntax that I'm missing?

What do you recommend?

[Simon Lloyd]

If your listbox was 343 then thats what that first line should read, you may be changing the listbox name incorrectly, do not rename your listbox change the code to suit it. Have you saved your workbook as a macro enabled workbook? if so re-save it as am excel 2003 compatible and upload it here (the reason for this is because not a lot of folk have 2007) and we will take a look!

[teachmedave]

Here is the file. Thank you very much.

[Simon Lloyd]

Your problem is this!, you have created your listboxes from the Forms toolbox instead of the Controls toolbox, you need to use the controls toolbox listbox in order for the code to work, you can then assign the listfill range to a named range for each listbox, you do not need code to enter the selections in the comments feild you can use the linked cell property directly under the ListFill range to assign a cell for the selection to appear in.

Once you have done these things if you are still having trouble then post back.

[teachmedave]

How do I get my selections into the control listbox? In the other listbox, I was able to just click on the cells that I wanted to use. What do I need to put in there?

I've been changing things around and was able to get it to work by changing it to a single selection listbox and putting the fill range on the same active sheet. How do I get it to work with multi-select and fillrange on the other worksheet?

[Simon Lloyd]

See attached

[teachmedave]

Thank you for taking so much time and holding my hand. This was excellent! How much more difficult is it to have multiple selections and populate them in a cell or cells?

[Simon Lloyd]

Hmmmm, now you have a little knowledge and an example workbook to work with play around with the settings, perhaps record your actions using the macro recorder, it will be a good experience for you understand how these controls work or can be made to work. Have a go, see what you come up with and post back.