How do I code an Edit button for a userform?

Howdy,
In the attached workbook there is a userform that once it is filled out and the submit button is clicked, the information is transferred into the excel spreadsheet. This works perfect for what I need it to do. There are a couple of other buttons that have been deactivated for the purpose of this sample workbook so no issues there.

Here is my dilemma:
Thanks to "layer 8" issues, I had to add an Edit button to the userform. When it is clicked, the user is to enter the row they want to edit.
1st) Is it possible to edit based on the row that the user enters?
2nd) If so, I have no idea how to write the code for this. What I would like to happen is after they enter the row# and click OK, the userform opens with the information from that row populated in the proper TextBoxes and ComboBoxes and then they edit what they need to.
Now I realize that I may need another button for them to be able to update that row. Sheesh!

Anywho, is this possible? If not what is an alternative method. If it is possible, is it the best method to use?
EOSR Sample for EF.xlsm

PS I have researched this and found a few posts here and on the web, but only one was remotely close to what I wanted (I thought) but when I read into it more, it wasn't.

I am looking at this for you.

This is a flag so I can find your post again as I get logged out almost immediately.


You need to think again about using the row number, that will not be viable if you have 20000 rows

Think about username and device number.


Ok this is very easy as you have done 99% of the work already.

you have the routine

Please Login or Register  to view this content.

Which is writing your data to newrow.

So make newrow a global variable for the userform by declaring it as a public variable at the top of your userform code.
Define an Edit Flag as a global variable using

Please Login or Register  to view this content.

Set this to false when you initialise the userform.

Now all you need to happen when you Click on Edit is for the Data to be loaded from your data and newrow to be stored
[ I will show you how to do that in a bit ]
Then EditFlag need to be set to True

Then the edit button needs to call your existing save routine:-

Please Login or Register  to view this content.

We need a small change in that bit of code too.

Please Login or Register  to view this content.

Becomes

Please Login or Register  to view this content.

Now all you need your edit button to do is to set newrow to the edit row when you choose the row to edit.

So I will code the Edit Button For You

Thank you very much mehmetcik!

mehmetcik,
I did consider username, device number, lot number, part number and even date. The problem is that one user can have multiple entries for the same device number, P/N, L/N on the same date. Then the user from the next shift could have the same information multiple times on the same date as well. The only thing that might not be a constant is column H, the details column, but that's not a guarantee. This is the reason I was thinking of going with the row number.

Can You not Create a Reference Number of Some Description

Can You not Create a Reference Number of Some Description

Also you should be using data validation.

I'm pretty new to this so I have no idea how to answer that question as I don't understand what you mean.
Also, I'm not familiar with data validation.

Ok this will take a while.

You have a hidden sheet with your masterlist that you are using for some of your data entry

you should be using that for all your data

I have created some dynamic named ranges on the attached document.

Run the macro test to see how they work.

They are similar to rowlist

Just something to look at for now as it is late here

Couldn't you have the user double click the row they want to edit?

Then you could use the sheet's BeforeDoubleClick event to load the form and populate it with the relevant data.

You'll probably want to store the row number to use when you send the data back to the sheet from the userform.

Norie,
Great suggestion! I didn't even know that was an option.
bakerman2,
Sweet!I believe the update you provided will work out for me.
mehmetcik,
I tried yours, had to change a few things, and will look at it some more to figure things out with it. I would still like to see what you come up with.
Thanks to ALL of you for helping me with this!!!!

No problem, it was well deserved.

Ok Sorry about the delay.

I rebuilt your userform because there were a few tricks that you missed out on.

Try this version for size, it should be quite intuitive,

however in edit mode you need to type or select your entry in a combobox and then click on another combobox to start the search routine.

Enjoy.

mehmetcik,
No worries about the delay. You have really rebuilt what I originally had. I haven't had a chance to go through it all yet, but there is one thing I really like about it so far. I love the fact that the user can add info such as a defect reason if it doesn't already exist. That was going to be one of the next things on my list to research and/or post in here. Now I don't have to thanks to you!
As soon as I get "down time" at work, I will look into it more.
Thank you very much for providing me with your version.

I'm afraid I screwed up.

I worked out how to sort the Data as you add new data, then forgot to include it into my Rebuild. Duh.

So i am posting it for you before I lose it.

I also put some conditional formatting in the Data Sheet to color alternate rows.

The final bit of code makes column C's format General, this is because your press numbers confuse excel.

Please Login or Register  to view this content.

Thank you mehmetcik. I still like the idea that a user can add new data because for 1 or 2 of the columns that will be helpful to me. However, I/we can't have them doing it for all the columns because some of them are "static"(?) lists, like the equipment# (press number) list, this cannot be changed by anyone but me. It has to be in a specific format and specific order and we have already discovered that the users can, will and have used up to 4 different formats for the equipment #. Going back to my original sample file, the employee name column is actually populated with the "currentuser()" code and the user never even sees this textbox on the form as it automatically filled out for them, so this one cant be changed by the user.
Basically I would like them to be able to add new data to the defect column and MAYBE the part number columns only.
The reason I originally had the sheet in a table format was because management wants to be able to open the workbook and filter by press#, defect, employee (another reason employee can't be changed by user), and/or date, etc. etc.

I thought you might want some controls but I left that for you to do.
You created the original name check so I thought you might be able to adapt that.

All you need to do is modify the new entry routine.

this line

Please Login or Register  to view this content.

just above the 'New Entry comment would do the trick.

simply check username and if it is you then allow any value of choice

If anyone else then accept only the specified columns.

You can filter simply by selecting the required field in the combo boxes giving you 5 filters

Okie dokie, thanks mehmetcik. I will check it out as soon as I get "free" time.