Edit/Update list from userform (auto alphabetized)

Hi

Sorting updated data:
As you are using an event to sort the data in column A, why not add an instruction to copy the data from column D into column A before the sort? That way you (a) don't have to add the data to both columns, and (b) when you have data in column D updated, it will be selected and sorted.

Updating Data:
Your form really only has the option to add new data, or close. Are you requesting that your form is enhanced to have an update facility?

rylo

rylo,

This sheet is part of a game pool, and the names are people who have entered. My thinking on this was that since the names are not entered at the same time and not everyone would pay their entry fee at the same time I needed a way to track and update as necessary. The plan as I see it is that as names come in they are entered into column "D". If the entrant has paid their fee, supplied an email address and given a tie breaker score that would be entered as well at that time. For the entries who pay later or supply information later I need a way to update that.

The numbers in column "C" are used as a reference for a list of entries not payed and the letters in column "E" are for a column lookup elsewhere in the spreadsheet.

Their may be 50 to 60 or more entrants and column "A" is alphebetized to make scrolling down the list easier.

My thinking was that if a correction in spelling from column "D" needed to be made it would need to be reflected in column "A" or the lookup (see dropdown box in the green cell) would not work.

It may be that that will create too many problems in which case just being able to edit/update columns "F", "G", and "H" would be fine.

I am not very savy in the ways of VBA and any assistance would be a big plus.

Thanks for your response.

Jim O

Jim

If you change your change event to

Please Login or Register  to view this content.

then changes made to column D will be reflected in column A.

Your form only has one option - add a new entry.

Do you want to have 2 different forms - one for add new entry, second for amend existing entry? Or do you want to have one form with 2 different options - add new or update?

rylo

Leith,

That is what I was hoping to achive. However I have found a few minor glitches.

1. It will not allow me to edit an existing name. It will only accept a new entry.

2. When a name is added or an entry is updated the text boxes and check box do not clear. so if you are not careful the next entry will have the same data.

3. I need to limit the range when deleting an entry to columns C thru H not the entire row.

That aside it is beautiful.

Jim O

I was looking at the application further and realized I probably need a list in column A and D.

Column D and the column reference E are used to find data in 5 other sheets that are posted and locked into that reference. If column D keeps changing alphabeticaly each time a new name is entered it changes the column reference as well and will cause a great deal of confusion as the references will not be valid.

Using combination of rylo's and Leith's code seems like the best way. This sheet just evolved and has grown since I started it last spring. I would have set it up differently now if I knew then what I know now, but hind-site is a wonderful thing.

I'll keep you posted.

Jim O

Leith,

I added a sheet to help clairify the relation between columns A, D and E. It has a similar function to the six other pages that the name and column lookup (the green cells) are linked to. I added "rolo's" code to have column A, update with any change to column D.

I also changed the form but not all of it is funcitional. I am not sure of the code to have the combo box lookup the data and populate the text boxes. If the combo will "Find" a name and populate the text boxes can a "Save Changes" button complete the operation?

The delete entry function is probably not necessary.

Getting closer.

Jim O

Jim

Here's a bit more for you.

1) Change the formula for PlayerName to

Please Login or Register  to view this content.

2) In the form, right click the combobox, properties, and insert playername in the rowsource properties.
3) Change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

4) Change the other code as below

Please Login or Register  to view this content.

See how that goes.

rylo

rylo and Leith,

Thank you both very much for your time and effort. This site is just teriffic!

That appears to get the job done.

Again Thanks.

Jim O

Hi

Why are you trying to add a formula to column D? Change the code to

Please Login or Register  to view this content.

then delete out the commented row. You only need to add the new entry to column D, as it will automatically be added to column A, and the defined name will automatically cover the additional item.

rylo

rylo,

Thanks, I have added the change to the sample and it is working now. However I tried to add the changes to my actual worksheet and I keep getting a Run-time error '1004': Unable to get the Match property of the WorksheetFunction class, and the row that is highlighted is;

DataRow = WorksheetFunction.Match(ComboBox1.Value, Range("D:D"), 0)

I tried to upload a copy of the worksheet but for some reason the upload keeps failing. As it is getting late here I will fuss with it tomorrow, I may have to recreate the sheet from scratch.

I will keep you posted.

Jim O

I think I have figured out what the problem is but I don't have any clue how to fix it.

This project has gone through many changes over its creation and now I have run into a conflict. The attachment I hope will make things clearer.

In the file I tried to upload yesterday I had deleted columns A-F on the data entry page and that was giving me the run time error.

Jim O

Hi

Problem is caused by moving the button to another sheet, and then not specifically nominating the sheet that has to be actioned. Normally in a situation like this, the button is on the sheet that you want to action, so you don't need to specifically reference a sheet as it will just work on the active sheet.

See how this goes.

Please Login or Register  to view this content.

rylo

I had figured out that it was not finding the correct sheet but had no way to rectify th situation. I was able to patch something close by adding a reference to the sheet in the code, but it presented other issues.

Your soluition is much cleaner and I thank you for your time.

Jim O