Creating search and update command buttons in a userform [Solved/Closed]

Posts
1
Registration date
Friday June 28, 2013
Status
Member
Last seen
June 28, 2013
- - Latest reply:  ShezzyWiggle - Mar 24, 2019 at 09:51 AM
I have created a userform for inputing new records in an Excel spreadsheet. I have written the code for the Save Record and Close Form command buttons already using VBA code.

Now I want to add a command button that will perform a search of a specific field (LName) in all records.

I also want to add a button that will update a record that has been modified.

I'm not sure what code to use.

Thanks for your time.
See more 

6 replies

Best answer
approved by Ratnendra Ashok on Sep 11, 2019
Posts
4
Registration date
Saturday March 21, 2015
Status
Member
Last seen
July 12, 2017
8
67
Thank you
Hi ,

I made an advanced userform example .
Really it's many features .
- Data Adding
- Data Delition
- Data Update
- Searching with The Userform
- Progress Bar Feature
- Scrolling the Listbox with Spin Buttons
- Next ,Previous ,First ,Last Recording Buttons
- Sheet Hide / Show Features with Toggle Button

Say "Thank you" 67

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 5708 users have said thank you to us this month

PS-C21H
Posts
1
Registration date
Tuesday August 16, 2016
Status
Member
Last seen
August 16, 2016
-
This is wonderful! Exactly what I am needing. May I also have a copy of the file?
Thank you!
> PS-C21H
Posts
1
Registration date
Tuesday August 16, 2016
Status
Member
Last seen
August 16, 2016
-
i can have that code..
Hi, Can you send it to ***@***. thanks ! I will really appreaciate it.
Thank you very much. May I have the code please? Please send it to ***@***
Hi there! I have been stuck on the code for my userform command button search for so long now (i have the load, clear, delete and review command buttons down) but the search function is critical to the workbook functionality and i cannot deal with any more debug errors!! Your example of the above userform is over and above what i have been looking for, so wondered whether you could please share a copy of this with me to give me a pointer as to where my code is going (horribly) wrong! Any info or advice you may be able to give would be massively appreciated! Email address is ***@***. Thank you kindly for any help!
Posts
3
Registration date
Thursday March 17, 2016
Status
Member
Last seen
December 27, 2017
1
5
Thank you
Is it possible to download the file pictured at the top? I would very much appreciate being able to study the code.

It is easily possible I am missing something obvious to do so (I'm getting old...)

Thank-you!
3
Thank you
I have uploaded the file to this link

[code]http://speedy.sh/HavTK/sample-worksheet.xlsm/code

On the worksheet tab named Input Master there is a button that will take you to the Input Form at the bottom of the Form there are 4 command buttons.
Add Record - is working fine and when you fill in the form and click the button it adds a new record to the Input Master worksheet on the next available row then closes the form
Search Record - is not programmed - I would like this button when clicked to open a search function that would allow you to find records that already exist
Update Record - is not programmed - I would like to be able to update a record that already exists by clicking this button - I do not want to create a new record when this is clicked just update the existing record with any changes/additional information that was input on the form
Close Form - is working fine when clicked it closes the form
Posts
2570
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 3, 2019
378
3
Thank you
Hi jbooth,

Hopefully you will like the following solution:

Open userform, enter data in userform, click on "Find Record".
A sheet will be made visible with all the records with the same ClientLN or Coach.

On this sheet (Search results) there are 2 buttons: Update and Cancel.
Cancel: Remove search results, hide sheet and return to "Input master" sheet.

Update: First select the cell or row that you wish to update (this is also what the button tells you) and click the "Update" button.
A message box appears if you didn't select a valid record.
A message box appears displaying the selected record and the record initially entered in the userform.
This is to confirm the update action. So either click on Cancel to stay on sheet and possibly choose another record to replace or click on OK. This will update (basically replace) the record on the "Input Master" sheet.

The only way to leave the "Search results" sheet is to use the buttons.

Note that the "Update" button is fully called:
Select row and hit this button to update record in Input Master sheet.
Note that the "Cancel" button is fully called:
Cancel search action.
Finally note that the "Update Record" button on the userform lost it's intended purpose and can be removed if you choose to work with this solution.

Here is your file:
http://www.ge.tt/1GdaGpl/v/0?c

Although it is not exactly what you asked, I think that it works pretty nicely.

Best regards,
Trowa
Posts
2570
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 3, 2019
378
2
Thank you
Hi Janb,

Could you upload your workbook to a filesharing site like www.speedyshare.com or ge.tt for better understanding of your query?

Also be as specific as you can as to what you want to accomplish.

Best regards,
Trowa
Posts
2570
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 3, 2019
378
2
Thank you
Hi jbooth,

Search Record - When does a record already exist? When all the cells column A:S are the same as another row?
Just checking in case you have other thoughts.
How do you see this working? Highlight duplicates, Delete duplicates, ....
Do you want a general search of the sheet or search for the values entered in the userform?

Update Record - How do you visualize this. Do you want users to input a row number and then replace that row with the data entered in the userform? Or ...

Best regards,
Trowa
The Search function should search for any records using the ClientLN or Coach as a search parameter. Where more than one match is made there should be an opportunity to scroll through the matching records until you find the one you are looking for.

The Update function should only work after you have searched for and found an existing record that you want to update (make changes to). Once you have made the changes and clicked Update it should replace the existing record with one that includes the updated information.

Thanks for getting back to me. I hope this is clearer.

Regards,
jbooth
TrowaD
Posts
2570
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 3, 2019
378 -
Hi jbooth,

Working on solution.
Need some time though (don't have much spare time at the moment).
I will get back to you next week.

Best regards,
Trowa
> TrowaD
Posts
2570
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 3, 2019
-
Hai Janb

Can you show me the code to create that userform because It really help me..to my Internship.
Thanks I appreciate your help.