VBA Userform - Search, Edit & Update functionality

Hi, welcome to the forum.
Read your PM, will see what I can do for you, wondering why the zip file? No problem to unzip but just curious.

Hi Motoka,
Before I dig into this.
The update userform as you mention is a 'replicated' version of the input form.
This is an overkill and makes it very unfriendly maintenance wise.
How is your VBA knowledge? As far as I can see at first sight of the input form your starting.
Does the input form work as it is now?

First suggestion:
Add a worksheet and place all the drowdown list items in separate columns with as header the name for that list
You don't want to edit your VBA code every time a new item is added or removed or needs to be edited.
This is one step to userfriendly maintenance.
Of course we can all paste and copy and create them for you but ... we just try to help and advice, building entire applications is all together another thing

This is one example of incorrectly placed code:

Please Login or Register  to view this content.

The marked text in read should be a separate macro so you can invoke it from other sections.

See below

Please Login or Register  to view this content.

Another important thing. which are the mandatory input fields? Have you thought of that, you cannot just update a database and 'forget' manadatory fields, your data will be worthless unless that is clear and forced before the user presses Add Input or Add record or whatever

You've still got some homework to do

I still think you need to mark mandatory fields and then for example enter missing or required as text, could also help when looking for records that need to be edited for completion.
I'll see how far I get, BTW according to the file size unpacked I don't see a size issue, but, we'll see,

My fist idea, no code just to show.
Have to go away now but this is my idea for the Userform

Here you have a working example.
I added a column with automatic number,if you edit a client excel uses this number to find the client.
Have a good look at the names i use for the controls (textboxes, comboboxes)
If you look at the Listbox click event there you can see wy I use these names.
I also work with a real table in the MacMonitoring sheet.

@dotchiejack: looks great, the light blue and white letters are less, but that's a user's preference.
I would add prompts verifying is you want to Add, Edit (missing Delete) and yes the real table that was something I didn't mention to the OP but makes it manageable.
You did a good job, I can lay back and rest

@ Keebellah
I used the blue from the labels of TS.
for the rest I made an example according to the title "Search, Edit & Update functionality "
@ motokaxperts
I'll make the required adjustments tomorrow, now I lay back and rest.
@rakotonirinas
1. make a table (in my example data_tbl)
2. select the filled cells in column A from your table (one record in the table is enough)
3. Click new.

Using Dotchiejack's file I made some changes and added the calendar entries, you will have to set it to your date format.
The DELETE record is not written.
Still much fine tuning to do but you'll work it out, was fun

filled some cells

You have to select all the filled cells.
What I mean by one record is enough.
If only cell A2 is filled than select that cell to create the range. (Don't select A1 = header)
If more cells are filled in column A then you have to select all these cells. (Don't select A1 = header)

Calendar pops up only in calendar fields

Got your PM, understood, still, it can come in handy to do it using a button on a userform
I must add that dotchiejack has not taken advangate of all the table functionality but it works great

I had already assumed about the Total Gain.
Delete is fixed (coded)
This link explains all about tables, interesting to dig into
https://www.thespreadsheetguru.com/b...t-excel-tables
File's got a new name.
What do you mean with the 'Open Query'?

Have you even tried entering a number in the gain fields? If
The fact is only numeric input is allowed and since you already mention that the values are in £ all you need is the amount
You keep mentioning the Surname, why don’t you tell me which one you mean? You can edit it yourself if look at the code

The reason is that you need to click on NEW and then you start to enter data.
When you start the search is the default.
About the gain, only numeric values allowed since the pound sign is already in the cell formatting of the table asn you mention the pound sign in the form.
I did not see any decimal values so only whole numbers are allowed, no decimal sign and since you have the code now you can work on that and change whatever you want.
I still think (suggest) you need to set a list of required fields and check these BEFORE saving a new or edited record, that's how data manipulation en data entry works, if data is incomplete well, it's the responsibility of the person or persons entering data. This if you want serious and trustworthy data

I think with the attached PDF and the file you will get the gist of it all.
VBA is not at all that difficult and it all boils down to logic and step-by-step instructions.
Read the code like you would a book, line by line, chapter by chapter, the same way you can write VBA code if the value of a is equal to the value of b then do this and that else do something else unless z is equal to y etc, etc, etc.
Important ingredients are Time, lots of it, imagination and perseverance (for troubleshooting) and you'll get far.
We all stated with the message box 'Hello World'
Enjoy your weekend

It doesn’t confuse me but you’re the one working with it
All you need to do is add Cmd_00_Click as the last line in the Userform_Initialize section, just before the End Sub

@Dotchiejack: a cosmtic tip, I ran into the similar lists in other posts and was finding a simple way to solve id and I think this one does the trick.
You seen when a user starts typing in the search box he/she can go on typing even if there is no match, like if the user type farmmmmmmmm all the m's continue but there is no hit.

I added the red portion and the it just stays showing farm even if you time more m's or whatever after it.
Give it a run and you'll see what I men

Please Login or Register  to view this content.

I noticed I had forgotten the code for the Diagnose date
It's there as well as my 'cosmetic' correction for the search box

As said the updated V1.2 file in the post, defaults to NEW when opened and every time the record is saved.
Mandatory fields at least Surname and DOB

Didn't test that, probably because of the fact that you want it to start in NEW RECORD more.
Will check and see if I can avoid the crash

Fund it, won't go into details but it no longer crashes.

Joint effort, dotchiejack had the greatest share in rearranging the form into a logical way and the listbox, I just did the functionality

There is an option, but it doesn't zoom correctly, I don't know what dotchiejack did but the last frame on the right does not fit in as it should.
This is the code I have but it doesn't doe a grate job

Please Login or Register  to view this content.

Just past the code above in the macmfrm (userform) code, no exta just past it there and open the form.
If it's no good, just remove it.

The code I sent is not really good you could just for now manually set the userform's zoom factor to 75.
Add the line in UserForm_Activate

Please Login or Register  to view this content.

Ýou need extra code that measures the width of your screen and then set the zoom factor accordingly.

I did some searching and tried something out, I don't have a laptop to test it but could you give this a run?
It determines the initial zoom factor based on the screen's resolution

You have to play around with this part od the code, the tZoom value is the one you need

Please Login or Register  to view this content.

I've got a laptop with a smaller screen (11") and will try it on that one tomorrow and if I get the fit right I'll let you know

Hi, I think I got it working like I wanted and you needed for your use
Based upon the screen resolution the userform's zoom factor is determined and resized.
If the monitor is a 34" then the userform will not go to 125% but stay at a 100% of the original size, only lower resolutions and smaller screen sizes will be processed accordingly.
Give it a run and let me know, I think it works.
Tried it on a 15" laptop, the 11" tablet is disconnected at the moment.

File version upped to 1.2.2

Edited:
I see what you mean, back to the drawing table

Ignore the quote below

If I understand you correctly, is this okay? Or do you want it bigger on the desktops?
You could play with this line:

Please Login or Register  to view this content.

Change the 1 to 1.2 (and not greater than 1.25) I think and see what it does

Will test what I have now tomorrow, is past midnight

Well, entirely different approach and the extra functionality to allow resizing the form when you click and drag a border.
Can you give it a try? It looks okay on my 11"tablet
BTW you may not add a caption to the userform's title bar, if a caption is present the resizing is blocked
Let me know how it goes

The screenshot with the error message isn't attached or is corrupted, cannot open it.

You're doing something wrong this is the result

What's the error anyway?

I tried it all but no errors,
I did place the bottom buttons in a separate frame

Thee is a point that it’s maximized for the laptop, no way I can change that, or at least know how to overcome this

I think that the reason this happens is that a certain point the userform snaps to full screen when expanded and then the resizing is indeed impeded

Hi, Will you please be do kind as to create a new post?
Thank you