Add Records Then Sort

[ScherzoVivo]

Hi Excel Friends!

So I've been trying my very hardest to code this myself before having to ask for assistance... but I'm exasperated, so hopefully everyone can be as super helpful as you have been in the past.

I need records, as they're inserted via a UserForm, to appear at the top of the sheet. I figured the best way to achieve this would be to create the new record at the bottom and then have the sheet auto sort it based on descending Record Number. My issue is that, while the data is being input correctly, the sheet isn't sorting it to the top. I used to have it kind of working, but it would start overwriting random rows of data in the list, so I quickly deleted that code.

Anyway, hopefully someone can offer some suggestions on this issue... or even a better way to reach the desired end-goal!

[dotchiejack]

Hi,
I reworked your workbook.
The main question was that the data has to appear at the top of the sheet, now it does.
I made tables.
You used AddItem in your code, try to avoid it because it is slow.
I added a list where you can choose a record to edit.
You will see that a use T_01, T_02 ... to name the userform controls.
This method will save you a lot of coding.
Look at the listbox click event and you will see what I mean.
If there are questions, do not hesitate to ask.

[ScherzoVivo]

This is quite possibly the most amazing thing ever. Thank you SO much for your edits! Now maybe I can figure out how you did what you did.

EDIT: Just looked over the code and I'm pretty sure I just had a stroke lol

[dotchiejack]

Thanks for the feedback.

[ScherzoVivo]

If you have some extra time to go over a few of the things you did via private message, that would be pretty cool.

[ScherzoVivo]

One more question for you! So I asked about hiding the Record Number and Timestamp and you stated that I only needed to change the "Visible" property to 'False' but that seems to hide the entire results box and not just that single column. Is there a way to assign those values to the data table but not display them in the userform?

The issue I'm having right now is that the form is too long for the screen when we open it on our computers. That, plus I'm trying to cut down on the data our other agents have to view when submitting/editing a guest issue.

[dotchiejack]

Hi,
Is this what you looking for?
I have hidden the 2 textboxes and set the 2 columns wide to 0 and list box width adjusted.

[ScherzoVivo]

Brilliant, that looks beautiful. How do I pay you? Ha!

I'm getting an error when searching through names in the userform. Looks like any time I enter a character that doesn't match something in the records table it crashes. (If I searched for "Se" it would display Sephiroth, Selina, etc. but if I search for "Sev" it will crash since no names match that criteria.)

It's pointing me toward the "ReDim nwlijst(arg - 1, 9)" string in the "T_09_Change" sub.

[dotchiejack]

My error I forgot the error handling.

[ScherzoVivo]

Just wanted to let you know that your setup got major praise from my coworkers. So thanks again!

Any suggestions for a good place for properly learning to code VBA so next time I don't have to just piece together code from across the internet?

[ScherzoVivo]

Hey dotchiejack,

Hoping you can help me a bit more with my spreadsheet from mid-last month. The coworkers are wondering if we (you) can add in search boxes for department, room number, and completion status. I'm slowly learning to write VBA, but your code is still just too complex for me to work out a way to add in that functionality.

Again, I appreciate your help so much up to this point!

I've attached a copy of the current workbook so it has a bit of real data to play around with.

[dotchiejack]

Hi,
In attachment, file customized as requested.
(I guess with completion status you mean the column resolved)