UserForm - Editing data via TextBoxes from ListBox

Hey Folks,

Covid Lockdown has seen me create a new Excel system for my work, im all but done but come into 1 small issue, i wonder if you could point me in the right direction.

I have a Userform that consists of;
3 text boxes
1 ListBoxand
3 buttons (Add, Update, Delete)

The ListBox source is a basic 3 column table (Employee Number, Name, Telephone Number) within the workbook. I have managed to get the data to show in the textboxes when i click on the row in the ListBox, and i can both add and delete rows in the ListBox, but the edit or "update" is where im struggling.

I have the following code attached to my Update Button (which i got from another user online, iv just mastered most Formula and im new to the VBA scene)

Please Login or Register  to view this content.

When i click on a row in the ListBox, it come up in the text boxes as wanted, i can then change the data in "TextBox2" and click update and it will update in the ListBox and on the sheet, but for some reason it wont work with the other 2 TextBoxs

Iv tried for 2 hours trying to edit it and test to see if it changes, but when i edit certain parts it just stops letting me update anything at all.

Hope you guys can help

Thanks in advance for supporting my evolving knowledge base

Matt

Originally Posted by AlphaFrog

Please Login or Register  to view this content.

If you change TextBox1, it will never match the sheet in column A and change it.

It's hard to tell you how to fix it without seeing all your code.

How do you populate the listbox?

I populate the listbox by putting the DNR in the rowsource in the listbox setting. I’m really out of my depth here 😂

Hey Alpha,

I tried your code, but it still only allows me to edit 1 feild (TextBox1)

I coped this wb and shrank it to only this form and sheet in the hopes of uploading, but the forum wont let me as im new.

All the code i have is below. Everything works apart from the update.

Its frustrating as this 1 thing will complete the whole wb.

Please Login or Register  to view this content.

Sheet1 is it really Sheet1 or Sheets("Sheet1") - just grasping at straws - your code should work - have you seen the big yellow banner - how to upload your workbook.
torachan.

Originally Posted by torachan

Sheet1 is it really Sheet1 or Sheets("Sheet1") - just grasping at straws - your code should work - have you seen the big yellow banner - how to upload your workbook.
torachan.

Iv managed it

It brings up a Compile error if i add the quotations and parentheses.

It would appear that the 'accursed' rowsource was not co-operating, I do not now why as the code looked OK.
to quickly prove it I have used a Table to load the listbox, and prove the 'update'
I will look again tomorrow and alter the complete code to demonstrate the advantages of the Table approach.
torachan.

Originally Posted by torachan

It would appear that the 'accursed' rowsource was not co-operating, I do not now why as the code looked OK.
to quickly prove it I have used a Table to load the listbox, and prove the 'update'
I will look again tomorrow and alter the complete code to demonstrate the advantages of the Table approach.
torachan.

Originally Posted by AlphaFrog

torachan is correct. RowSource is the culprit.

So when the Update_Click procedure updates the 1st cell in a given row (before the other two cells), the listbox immediately updates because of RowSource. This in turn triggers the ListBox2_Click procedure and that code refreshes the other two textboxes with their old values erasing any changes in those textboxes.

This code will apply all three textboxes en masse to a row

Please Login or Register  to view this content.

What an absolute pain in the ***, iv probably over complicated whats needed here, but it will be alot more smooth and better looking (UI wise)

Thanks guys, appreciated.

As previous posting.
Updated code facilitating Table data storage.
Advantages - elastic - self-sizing - no need to constantly check size for each entry/deletion.
Placing your declaration of variables at the head (not in each sub) makes them available throughout the form.
e.g. the listbox.listindex is only read once and is then available to each sub.
I have used the mouseup event - this allows you to scroll down just holding left mouse button down then choice is made on release of button.