VBA Userform - Search, Edit & Update functionality

Dear Excel Forum users,

I am a VBA newbie and have created the attached database from scratch including a very basic Userform (linked to 'New Entry' in worksheet ÇC) which allows me to add a new guest to my database (refer to CC sheet only).

Whilst I have looked at the Excel Data Form as an option, it is clunky and I'd rather experiment with VBA to create something more slick and personalised.

I want to add into the existing Userform (in 'ÇC' worksheet) a 'Search' and 'Édit/Update' option , presumably using command buttons within the Userform itself. I have looked quite extensively on the internet but am struggling to come up with a clear code. When searching i'd ideally like all the criteria in the userform to populate based on a name search with a 'Find Next' function. When executing the Edit/Update option i'd like it to replace the row of existing data rather than populating a new row.

Apologies in advance if this a simple solution (and for my poor coding no doubt), I do not expect anyone here to code the whole thing for me, rather a few handy pointers would be much appreciated.

Many thanks,

Jake @ChiawaCamp

Dear Keebellah,

Many thanks for your prompt reply, much appreciated.

In essence, you've got it. I want to create a database that allows me to add/find/edit/update entries. My initial idea was that the 'search' worksheet would be a cross database search function that negates the need to search via a userform and will be used for general reference (i.e non editable). The userform (I have a separate one for the 'CC' and 'OM' worksheets) will be for adding/editing data. However, I am now thinking that it might be best to do it all through the userform. Perhaps the code would get too complicated for a beginner!

Any help/modifications/suggestions would be much appreciated. And yes, these projects are very organic with new ideas coming through all the time (I must have spent at least 60 daylight hours so far on this!!). I realise more and more that code needs to be 'clean & clear' so it can be easily referenced/modified at a later date!

A huge thanks in advance,

Jake

hi I've amended your userform, added Search, Amend and Delete

Please Login or Register  to view this content.

Hi it might be an idea to add an additional column and search with a Booking Reference Number this saves all the problems with customers having the same name

Dear Keebellah & Toonies,

Many thanks to you both for your excellent feedback and amendments. Extremely useful and pretty much exactly what I was looking to achieve. I have a few other ideas I want to try myself but will definitely contact you both for any further advice.

Very best regards,

Jake

Dear Keebellah & Toonies,

Many thanks to you both for your excellent feedback and amendments. Extremely useful and pretty much exactly what I was looking to achieve. I have a few other ideas I want to try myself but will definitely contact you both for any further advice.

Very best regards,

Jake

Dear Keebellah & Toonies,

Many thanks to you both for your excellent feedback and amendments. Extremely useful and pretty much exactly what I was looking to achieve. I have a few other ideas I want to try myself but will definitely contact you both for any further advice.

Very best regards,

Jake

Sorry guys, multiple replies due to internet outages here in Zambia.

Regarding the attached from Toonies, the userform will now not let me add a new entry. Is there a quick fix to this?

Best regards,

Jake

ok I've amended this part of the Add Button code and cleared all rows.

Also the sheet is then password protected by the userform - password = "password"
this is to stop data from being deleted accidentally.


from this

Please Login or Register  to view this content.

To this

Please Login or Register  to view this content.

Dear Toonies,

Many thanks, all fixed. Great!

Kindest regards,

Jake