Edit Data through UserForm

[sgp]

Hi

I have a userform that currently adds data to a worksheet and I was looking to add in an 'edit' button.- not sure about the coding

The fields that can be edited are:
- Requirement
- Name
- Agreed costs
- Supplier Costs

I would like the edit button to edit the existing data, so it will look up the requirement first in column A, then replace the 'name', 'agreed costs' and 'supplier costs' in that row.

I have attached an example on how the data will be stored.

Appreciate your support.

Thanks.

[jaslake]

Hi sgp

It would be helpful if your attachment included the UserForm AND the existing code.

[sgp]

Please see attatchment.

Thanks.

[jaslake]

Hi sgp
Can/will a given requirement (eg: Requirement 5) appear multiple times in Column A? If so, can/will those multiple instances have the same Preferred Supplier or could the Preferred Supplier be different?

[sgp]

Hi jaslake,

A Requirement can only appear once in column A, however the preffered supplier could change over time.

Thanks for your time!

[sgp]

Would it be possible if a Contract Requirement could be added and edited through one button?

I.e. if a requirement does not exist in column A then the data is added and if a requirement does exist in column A then the information is edited.

E.g. Requirement 5 exists therefore information can be edited in that row, Requirement 9 does not exist so it can be added below.

Coding exist in the UserForm attached in previous posts, may need some tweaking!

Many Thanks!

[jaslake]

Hi sgp

Try the code in the attached. Let me know of issues.
Please note the code requires a reference to Microsoft Scripting Runtime...Tools > Reference > Microsoft Scripting Runtime

[sgp]

Hi jaslake

That seems to be working well..thanks a lot for your help, much appreciated!!

The only problem i'm having is that the column 'Supplier Costing' may move across if more 'RPI years’ are added, in my previous coding the column 'Supplier Costing' is searched then the costing is recorded below.

Could something like this be incorporated??

Many thanks, you’ve been a great help!

[jaslake]

Hi sgp

Regarding this

The only problem i'm having is that the column 'Supplier Costing' may move across if more 'RPI years’ are added, in my previous coding the column 'Supplier Costing' is searched then the costing is recorded below.
Could something like this be incorporated??

Sure can...give me a bit of time...may not be tonight.

[sgp]

No worries that’s absolutely fine!! - I will examine the great coding you have done!

..also just to let you know that 'Supplier Costing' column may not always be the last column.

Thanks!!

[jaslake]

Hi sgp

This should NOT be an issue...the procedure is looking for "'Supplier Costing" regardless of where it's located.
Try the code in the attached...beat it up...let me know of issues.

[sgp]

That's BRILLIANTTT!!

Works really well, thanks for your help!

[jaslake]

Hi sgp

In response to your PM I'm posting the revised code

Please Login or Register  to view this content.

Let me know of issues.

[sgp]

Thanks jaslake, works really well!..appreciate your time!

[sgp]

Hi jaslake,

I did say the code works well but I have identified a problem with validations..(sorry!)...if I try adding a new requirement by filling in all the required information then the msgbox "A Value Required In All Feilds" still shows up...

However the validations work fine when updating requirements.

Thanks for your support.

[jaslake]

Hi sgp

'Course it does

if I try adding a new requirement by filling in all the required information then the msgbox "A Value Required In All Feilds" still shows up...

That's the way the code is written Sorry 'bout that...try this

Please Login or Register  to view this content.

[sgp]

Thank you so much jaslake, couldn't ask for more!!!! - You've been a great help!!

Thanks!