Help with VBA code spin buttons in my password record spreadsheet

[davidwh000]

Hi, can anyone help please.

I have created the attached spreadsheet, using several guides and examples on YouTube, to record all my password information and have a couple of problems with the VBA coding.

All works OK for searching and updating but I have a problem with the spin buttons. They scroll through the data and populate the boxes OK but I would like the resultant searched for line in the list box and in Sheet1 to have a highlight bar across the selected text, would like the scroll to start at the resultant searched data line and would like the spin buttons to search in a rotational way i.e. when it gets to the last record to start again at the 1st if I am pressing down and visa versa if pressing up.

I would also like the double click feature to select the resultant searched for line in the list box and highlight the same on the Sheet 1 data.

I would appreciate any help with the code in SpinButton1 and ListBox1_DblClick subs as well as any other improvements anyone thinks would be useful.

Thank you.
David

[Keebellah]

Hi, I've downloaded you file and will take look

[davidwh000]

Thank you, that will be most helpful.
David

[Keebellah]

Your file is quite a mess as goes for VBA structure.
You have used a listobject table that only has one row of data and the rest of the rows below do not belong to that list object, very messy.
The Spin Button does what you want it to do.

I suggest you read something about listobjects amd how to use them.
I converted the A1-L57 to a table but your code does not work with tables

[davidwh000]

Thnk you very much, I will take a look at the revised sheet. I am new to VBA and am experimenting so the feedback is very useful.
Regards
David

[Keebellah]

This is a good place to take a look https://www.thespreadsheetguru.com/

One of these here is especially for Tables and Listobjects
https://www.thespreadsheetguru.com/b...0with%20Tables

This will help you understand what I was talking about.
Just holler if you need help

[torachan]

My comments would reflect those of @Kelbalah.
I have attached a couple of links that prove useful when using tables - once you master them you will not go back to sheet/range referencing.
For the time been I have left the table in place although just used range/sheet referencing until you familiarize yourself with tables
I have tidied up some of the code mainly to introduce you to the method of looping to populate your textboxes.
In development of larger apps this saves 'acres' of code and keyboard tapping.
The spin buttons do as intended,the 'spinup/spindown' is not well documented on the 'web' I have also coupled them to your listbox cursor.
Happy coding, any queries post back on this site.
torachan.

https://www.thespreadsheetguru.com/b...t-excel-tables

http://www.snb-vba.eu/VBA_Userform_in_database_en.html

[davidwh000]

Thank you, I will take a look. Much appreciated advice.
David

[davidwh000]

Hi Torachan

Thank you so much for the modified file. I have a lot of learning to do. Probably need to go on a course.

The form works much better with your changes but I would like to add a couple of things and wondered if you could help some more.

Firstly the update does not seem to work in the revised version. If I change any of the data in the upper boxes, in my earlier version the record updated to the newer data when the update button and confirmation was pressed. Would this be possible in your version?

I would also like the search to search on parts of the text in the Accounts column and populate the boxes as it finds the first few characters and then I would like to be able to use the spin buttons to scroll up and down from that entry. There are some entries that have common letters in the first few characters such as YouTube David and YouTube Karen. I could make each record unique but this would take much management as there are over 500 lines in the data.

I would also like to start the userform with the first entry of my data already populating the boxes when the form is run and then be able to scroll down from there.

If you have time and coud look at these final changes it would be very much appreciated.

Thank You
David

[torachan]

The update does work - select record in listbox - make your alteration - press 'UPDATE' - confirm (messagebox) - look at listbox - new data is there and in sheet.
I have replaced your search textbox with a combobox as you type in the selection dropdown filters if the name is complete the data textboxes fill.
At startup the initialise places the first record in the form, also as you do the combo search the spin button value carries on from that record in the listbox.
Note: all variables are declared at the head of the code with 'Option Explicit' first, this makes the variables available over all Subs in the UserForm.
torachan.

[davidwh000]

Hi Torachan
You are an absolute champion. Well done, I am so impressed.

Only one thing would make it complete now and that would be that the drop down list in the new Search combo box shows the sorted list with the words in apha /number order so for example if you enter a in the search box, the drop down shows a then abc etc rather than the list in its table order and you can then select the wanted entry especially if the drop down list had more entries too.

Wonderful job.
Thank so much for this, it just shows that I have so much to learn.
Regards
David

[torachan]

The alpha-numeric sort is done during the save routine.
As the data is saved in a table I have used the sort method as appropriate.
Hopefully everything updates dynamically.
torachan.

[Keebellah]

Nice one @torachan

[torachan]

@Keebellah, thank you for your compliment, hopefully @davidwh000 is now a convert to the dark side (VBA coding)
torachan

[Keebellah]

Wait 'til he gets the hang of it
He'll be unstoppable

[davidwh000]

Hi Torachan

Got into a bit of a pickle trying to debug my latest problem whilst testing the delete option in the code that you so kindly modified.

If I search for an entry and then press the delete option it deletes all records with the same name from the data not just the currently selected entry line. It does not however delete newly added entries that have the same names. Going round in circles a bit and its a good job I backed up earlier versions as my fiddling has corrupted the operation of the delete option. All other features seem to be working very well.

I would also like the data, when sorting, to sort on 2 different columns with the first sort criterion being column C and then on column A so that accounts are sorted alphabetically but in their category groups. My VBA book asssumes a level of intelligence greater than mine.

Hopefully my last problems on this one. I still think a physical course might help me. Do you teach VBA?

Regards

David

[Keebellah]

VAB courses online exist and there are many, just google and you'll be overwhelmed.
The best learning school is trial and error and starting from scratch with simple things like a recorded macro and then start editing.
The advanced things like Torachan en many of us do comes with practice and time, loads of time, that is one of the most important ingredients aside inventivity and fantasy.
You could almost say that VBA has no limits except that of the programmer

[torachan]

@davidwh000, @Keebellah sums it up very well, time,patience,practice,logic coupled with imagination -VBA is 'Pandora's Box' - everytime you open it there is something new.
I did not want to change too much of you original coding - however the weakness of your search method not coping with duplicates has shown itself - I have now changed the delete/update search to the method I usually adopt - there is a direct link between the row in the listbox and the row in the sheet - eliminating any need to search for matching data fields - look in ListBox1_MouseUp event you will see a variable 'r' feed from the ListIndex - look at both the Delete & Update events that same 'r' associates itself with the row on the sheet - things to remember (listindex is zero based and need to compensate any row displacement on sheet).
I will look at your sorting request later as it may need a rethink of present code.
Attached also demo of what happens when bored - inspired by a game from a Christmas cracker.
torachan.

[Keebellah]

If you start with card A and embedded 11 in your memory then the answer resulting is 12, incorrect

[Keebellah]

For the sorting part:

Please Login or Register  to view this content.

Will sort Category and then Account

[torachan]

@Keebellah, I have just tried cardA x 11 twenty times in succession each time answer was 11
I will not sleep tonight until I too can crash it or find if my poor coding is carrying a spurious variable
I think I may convert @davidwh000's app to totally table based as it is moving slowly that way with each modification.
Thanks for code I will graft it in and post back.
torachan.

[torachan]

Another step forward.

[Keebellah]

Maybe your code doesn’t like a Dutchman using it 😜

[Keebellah]

Did a try again (mindpuzzel) and now it worked Don't know why it went wrong three times in a row.
Will try again tomorrow

[Keebellah]

I hope your forgive my editing some of the vba code, it's based upon Tora(v5)

[davidwh000]

Thank you so much for all of the work you guys have done to help me with this. I have fiddled each time I have a new version and experimented with what I have seen and can say that I have learned a lot.
Cheers for all you have done to make this project so educational to me whilst delivering a working solution to my data management.
I wish you all the very best for Christmas and hope that 2021 brings you and yours a happier year and that we all come out of the 2020 nightmare showing the care and helfullness you two have shown me over the past week.
Thank you.
David

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

[Keebellah]

Hi Dave, glad to have been able to help.
Best wishes to you too

[davidwh000]

Hi Guys

Just a bit stuck again. I have tried to fix the problem but failed so am reverting to you for help.

When pressing delete option for the first time the entry deleted is the first line in the data table rather than the one that has been entered into the boxes via the spin buttons or Search option. If I select the entry again the correct one is deleted so it seems the first press of delete after opening the workbook selects line one in the listbox.I am sure that the fix is probably quite simple but after a couple of late hours and several failed attempts I cannot fix it.

I tried by attempting to make the line whose data is displayed in the boxes at the top of the creen to always follow in the data table shown in the listbox. The only tme this cannot be done of course is when the record has not yet been added to the list but the tracking should happen if the line is selceted using the spin buttons, directly clicking on the entry in the lisybox or using the search option.

Help would be appreciated again please.

Regards and thanks in advance
David

[AliGW]

You'll want to remove the solved tag if you want further help.

[Keebellah]

Hi, I foresaw that already in the way the code was built,
Working with tables needs a different approach
I have some ideas I’ll workout and upload when completed
The changes wil affect the whole approach but will make it workable (I hope)

[davidwh000]

A little more information.

If I launch the project and search for an entry which I want to update or change or delete, even though the fill boxes show the correct line info and the right record is highlighted in the listbox, when I hit either delete or update the record in line 3 is either deleted or overwritten with the new data and not the selected line so it might have something to do with the initialisation bit but I cannot seem to track the exact problem. Unlike with Macros I cannot step through any processes to debug or am I missing a VBA procedure trick.
David

[Keebellah]

F5 in the vba takes you line by line through the macro you want to run

[torachan]

my initial code used the listbox.listindex to i.d. the row to delete.
with multiple search methods this method has become compromised.
there is a variable 'r' generated when the selection is made from the listbox.
now there is a need to link the three search methods to a common filter.
the reason that the first delete on opening removes the first row is that you requested initial population on opening therefore the selection had already taken place.
rather than cause confusion with differing approaches I will bow out at this stage.
torachan.

[Keebellah]

I'm working on a variation with the same Userform, hope to get it done and will upload

[davidwh000]

Hi Torachan
Thank you so much for all your help with this. I have learned much over the past 2 weeks though am far from profficient and it has allowed me to experiment and with the guidance from a couple of experts. Have a great Christms if you are able, and best wishes for the New Year.

[davidwh000]

Thank you Keeballah.
I look forward to the variation and whatever features you add to it.

I have been using the form and it is so close now, only a few small bugs but doing mostly the job I intended it to do.

I have tried the tidied up version that you provided i the last update and though it runs OK it crashes Excel occassionally saying that there is a memory error.

Thanks again.

Regards
David

[Keebellah]

Here's my idea of how i would do it.
The combobox has been replaced by a searchbox, just start typing and the listbox will filter as you type or select the item
Needs finetuning and smore code but this is the idea.

Have not added much information explaining my vba ideas, will do that and update again

[davidwh000]

Thank you Keebalah

I look forward to the final fully working version with the expanation as I think if I can better understand al the processes in this one it will go a long way to help me adapt instructions for other experiments. I think that finding sensible projects whilst learning is definately the trick rather than just reading and fiddling with code.
Have a good Christmas week.
David

[Keebellah]

What you actually need, and I won't go into developing this (yet) is several additional functionality like:
- email verification (check if the entered email address is in a valid format (there's code for that but needs to be implemented here)
- when adding a record, check if it's not already present, that means check the account column and website if these are both not already present in the same record
- check if all fields or the ones you want to contain data are actually filled
- the print command could use some extra functionality

So you see, programming and design has more to it than what you get a first sight, what happens in the background is just as important.
The file I uploaded is not entirely bug-free but I prefer a search box where you can search as you type, in this case you can type anything and if the combination is in any of the 12 columns that row will be shown, so even if there's text in the notes
Play around with it and see what you can do with it.
Explanation of the code will follow

[Keebellah]

Hi David,
I made some modifications to the Userform that way I do these things.
Programming si not a rule and many will do it other ways.
I did try to place as many comments as I could in the VBA code with short explanations.
Will keep you busy this Christmas
I'm attaching the file and a separate zip file with the contents of VBA Project as textfiles

Happy Holidays

[davidwh000]

Thank you Keebelah

Something to keep my mind working over the next few days as we cannot do much else this Christmas.
Hope you keep safe and well and have a good time over the festive period.
Regards and best wishes for 2021
David

[davidwh000]

Hi Keebalah
The changes are pretty good and the check for all boxes being filled is a nice feature to have but not great for the data that I am populating as there are some instances where data is not required or available so the part data cannot be added as a new record. I have commented out the references to the feature and it no longer gives me the error message but also fails to add a new record or update one without the filled boxes. Is there anything else I need to change rather than just commenting out the feature?
David

[Keebellah]

Hi Dave, commenting out the lines is not enough, there is a routine that checks the 12 text boxes, you will have to edit that code so that only those text boxes that you mark as required are completed before saving
If you tell me which I’ll try and work it out before Xmas

[Keebellah]

Okay, I made a minor modification to make it work.
If you look at the userform and check the 12 textboxes, I placed a tag value 1 in 4 that require to be checked.
So all you need to do is if you want other text boxes to be filled place a 1 in the textbox's tag field.

The only one that turns red if it's not empty but incorrect is the email address, or leave it empty or enter a valid email format