Can't get listbox which displays rows in sheet to add texbox value to selected row

**Julesdude** · 06-30-2010, 11:35 AM

Hi I really need help....with my listbox. I have a listbox that populates with specific rows from a worksheet behind it. The macro searches each row and puts the row in the listbox if it matches certain criteria, namely if column G is not empty and J > 0.

The listbox populates fine. It's what happens after which I can't get to work. I need the value entered into a textbox on the same userform as the listbox to be entered into a cell on the worksheet corresponding to the row selected in the listbox. This needs to be done after a command button is clicked on. At the moment I click on the button and it does nothing.

How do I do this?

Code for populating listbox:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

**royUK** · 06-30-2010, 01:22 PM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

**Julesdude** · 07-01-2010, 09:53 AM

Sorry Roy, yes of course. Here's a sample workbook with the code I am experiencing problems with above...

**royUK** · 07-01-2010, 09:57 AM

The ListBox doesn't even load.

I'll find an example when i get home this evening

**romperstomper** · 07-01-2010, 10:03 AM

I suspect it might be easier to store the row numbers in an additional column when you load the list box and then just use that value when writing to the sheet.

Edit: X-post here.

**Julesdude** · 07-01-2010, 10:07 AM

Sorry, my mistake. Try loading this.

**Julesdude** · 07-01-2010, 10:13 AM

Hi Romperstomper, yes that could be an option although I want to keep the sheet as 'clean' as possible really. The less surplus data the better. There must be a way I can get Excel to put the selected row into a 'find' routine and search back to the sheet with the end result providing the row i need the data from the textbox to go into.

**romperstomper** · 07-01-2010, 10:18 AM

I wasn't suggesting you put the row numbers in a column in the sheet, I meant in the listbox!

**Julesdude** · 07-01-2010, 10:21 AM

Ah I see

Hmmmm, hadn't thought of that. Not sure if it would be useful because the problem is that some rows on the sheet are deliberately missed out as they don't match the criteria I mentioned. So if we were to index in that way you couldn't correspond that index back to the row in the sheet, if I understand you correctly!

**royUK** · 07-01-2010, 10:23 AM

This example should help

FilterForm.zip

**romperstomper** · 07-01-2010, 10:32 AM

Originally Posted by Julesdude

So if we were to index in that way you couldn't correspond that index back to the row in the sheet, if I understand you correctly!

I don't think you do - I mean you store the actual row number of the data you add to the list box. Therefore when you select a row in the list, you automatically have the actual row number of its location in the sheet.

**Julesdude** · 07-01-2010, 10:39 AM

I see. I could then hide the column by just declaring the width to be 0 is I didn't want this visible. This could definitely work. But how do I retrieve the row number, put that in the listbox and then reference it after selection to put textbox value into the sheet row?

**romperstomper** · 07-01-2010, 10:45 AM

You would need to declare your array as:

Please Login or Register  to view this content.

then add dn.Row as the value in the last 'row' of the array as you populate it. When you need to refer to it, you can use the list property:

Please Login or Register  to view this content.

**Julesdude** · 07-02-2010, 05:33 AM

Sorry romperstomper, could you post what the code would look like altogether?

**romperstomper** · 07-02-2010, 06:18 AM

Code in the MainForm:

Please Login or Register  to view this content.

Code in the receiptform:

Please Login or Register  to view this content.

**Julesdude** · 07-02-2010, 12:21 PM

Thanks for the help so far. It's still not inserting that line for me though when I make a selection and click submit button. Just does nothing.

**romperstomper** · 07-02-2010, 01:43 PM

Worked perfectly for me in your sample file.

Can't get listbox which displays rows in sheet to add texbox value to selected row

LinkBack

Thread Tools

Rate This Thread

Display

Can't get listbox which displays rows in sheet to add texbox value to selected row

Re: Can't get listbox which displays rows in sheet to add texbox value to selected ro

Re: Can't get listbox which displays rows in sheet to add texbox value to selected ro

Re: Can't get listbox which displays rows in sheet to add texbox value to selected ro

Re: Can't get listbox which displays rows in sheet to add texbox value to selected ro

Re: Can't get listbox which displays rows in sheet to add texbox value to selected ro

Re: Can't get listbox which displays rows in sheet to add texbox value to selected ro

Re: Can't get listbox which displays rows in sheet to add texbox value to selected ro

Re: Can't get listbox which displays rows in sheet to add texbox value to selected ro

Re: Can't get listbox which displays rows in sheet to add texbox value to selected ro

Re: Can't get listbox which displays rows in sheet to add texbox value to selected ro

Re: Can't get listbox which displays rows in sheet to add texbox value to selected ro

Re: Can't get listbox which displays rows in sheet to add texbox value to selected ro

Re: Can't get listbox which displays rows in sheet to add texbox value to selected ro

Re: Can't get listbox which displays rows in sheet to add texbox value to selected ro

Re: Can't get listbox which displays rows in sheet to add texbox value to selected ro

Re: Can't get listbox which displays rows in sheet to add texbox value to selected ro

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions