Userform - Search&Update ability

Hello everyone, I'm quite new to VBA coding and thanks to my C&P/editing skills, I’ve managed to make an amateur CRM for my own needs in my company.

ss.JPG

I’ve come up with an user form(you can find its screenshot attached) which stores the data in one sheet(Maindata) and also creates a new another one(Sheet name is getting its name from center ID textbox- so its dynamic) which contains that center’s specific data and financial projections.

Let me give you a brief overview of the spreadsheet;

Maindata: Data entry starts from A2 and goes to AU2, (47 columns in total) the spreadsheet hasn’t populated yet, but when it does it will be probably +400 row long.

Basically what I want to achieve is; Search&Update ability within user form. I can’t bring pre-registered data back to my user form. The search should bring partial matches as well, so there could be more than one registry when searched. To select the right one from search inquiry, I’ve added a list box that should give me the search results inside and when double-clicked, it should bring the whole row’s data back to user form. At the point of update, it should update/overwrite on the related row(Should not create a new registry) and it should update the dynamic named sheet’s(Which is created with the same name with “Center ID” back at the registry) specified cells.

To be more specific with the search&update;

1. A user will type into "textbox1"
2. Hit search button which is called "cbSearch"
3. Matching registries will be listed in "listbox1"
4. Upon double-click userform will be filled with the selected registery info.(Listbox will show 4 cloumns of data but after dclick, the rest of the information will be shown on the userform)
5. Update button should overwrite on the existing info on "maindata" sheet and the sheet that matches with it's center ID.

If you can show me how to fill a textbox with the selected item's info from listbox I can adapt it to the rest of the 46 columns

"A" column's info should go into = TB0 "B" column's info should go into = STN

I'm putting the code of the save button which has the %90 of the code in the userform.

Please Login or Register  to view this content.

Originally Posted by Keebellah

I can see that you're quite new at VBA, the way you code everything, well we all started once and had to learn
With the code written as it is you make it very difficult and I could put time into it and rewrite, but this is a help forum there is a separate section for commercial services if you really want someone to develop it for you.
However I will make some minor modifications and try and give you some tips how to do some thing which you have made very complicated for yourself.
I really admire your effort and am sure you picked up things from the internet and even tried by yourself.
I still look for scripts and ideas how others do it, I am still learning every day (even after more than 40 years) and VBA is fun.
You need two important things and I hope your boss will allow you: Time ( a lot of it), patience and imagination; time is what is really important especially when trying to figure out why something is not working
If you answer the search question I asked previously I will see what I can do for you.

First of all, thank you for your time Keebellah it is much appreciated!

I know it will be a long run but as you said, VBA is fun! Especially, when you see the outcome! For many people, I'm sure this whole thing can be done in an hour or so, but I'm learning a lot of things on the way so time isn't an issue.

I want to run the search between A:D columns with a single textbox.

The reason behind all columns are showing up is; I couldn't pick the rest of the data from the list box to textboxes by setting a range between A:D Because, after the search, the user should click on the desired registry from the listbox and the whole userform should be filled with the data. I could only achieve this with this code, so I was working on it.

But, as you said, I think I've made it quite complicated for myself ha

I couldn't thank enough Keebellah, the form is way better now. Let me analyze the code you've modified and get back to you with my findings.