the code works, but the UserForm freezes for half a minute.

Any assistance in letting me know what I am doing wrong is greatly appreciated!

This UserForm (see code below) does exactly what it is supposed to do, but gets stuck in some kind of 'loop' and can take up to a minute to process.
I think it may help if I explain the purpose and process:
The Form is designed to build an individualized time-sheet. You select a name (cboName1) and a department (cboDept1), and the labels in the Form will be populated with the tasks belonging to that department. Next, you check any task (checkboxes) you want to be included, and click Submit (cmdOK).
You can repeat this for the same employee for/in different departments.
The UserForm populates three columns in a Worksheet ("ONE") and sorts that sheet. Another Worksheet (EMPTOW) then 'reads' this information (using Index, Match, Row) and places the data (Tasks and Departments) in the appropriate employee column. There are 100 columns, with headers (Employee names) that 'read' data from an indexed Employee list in another Worksheet.

btw: the time-sheet UserForms that are build using this UserForm are working great.

Thank you in advance,

John

Please Login or Register  to view this content.

I would suggest stepping through the code using F8.

Is there a specific line of code that take a long time to process? If so, which one?

Is there a loop that the code is getting stuck in for whatever reason?

Other than that, it's hard for me to say without seeing the workbook. Nothing in your code sticks out as the obvious reason why it's slow, at least not to me.

Thanks walruseggman,
Even after a year I'm still a beginner with VBA, so excuse any of my 'stupid' questions.
I've tried stepping through the code using F8, but something funky happens when I hit F8 on any line of code other than the UserForm_Initialize() sub.
It immediately jumps down to the bottom of the code, to the "Unload Me" in the cmdexit_Click() sub.
I can't figure out why.... Any ideas?

Thanks!

I'd have to try it out, sorry. Can you post the workbook?

See attached the workbook. This is a rough draft workbook without any features or formatting yet. I'm starting over building this model after my previous attempt resulted in a monster workbook (7MB, with 60+ worksheets) that completely cratered. So I am now adding one form at a time, and testing the performance with each step. That is how I ran into my first problem, which is the question I had posting above.
The finished model requires to have a number of additional features such as reports, dashboards, search functions, charts, analytics, etc. so I still have a long ways to go, but need to get over this hump first.
Thank you for any insight you can give me on the slowing down issue.

FS framework 0326.xlsm

So the code from userform 6 actually runs in about a tenth of a second (using Timer to measure). The problem seems to be that there are 4 processes on Sheet ONE that are calculated when something changes on that sheet. Those take 10-14 seconds to run for a sort, and they run because you sorted that sheet in user form 6's code. That's why your code is hanging; the calculation of those processes.

Try sorting the names on Sheet ONE yourself; you'll see down in the status bar just how long they take to run.

Does that help?

Thank you walruseggman,

I think you have identified the problem!
However, removing the Sort command from UserForm6 isn't solving it.
Even after removing the sort sheet ONE command from USerForm6, the form still hangs there for a long time, after I enter data and click cmdOK.
Also, when doing the sort on sheet ONE myself (as you suggested), the sort still takes too long, so there must be another problem somewhere.

I suspect the problem may be sheet EMPTOW. As soon as any data is entered into sheet ONE (from UserForm6), sorted or not, EMPTOW uses INDEX, MATCH and ROW to find and sort the data from ONE (you can see the formulas in each of the cells in EMPTOW). It seems to do this for each new record (added to sheet ONE), one row at a time.

If this is indeed what is slowing things down, what procedure can I add/write to make this process happen faster, different or in the 'background', and have UserForm6 Unload immediately (or 1/10 of a second) after selecting data and upon clicking cmdOK? I've tried adding the Application.EnableEvents = False to the sort command, but it doesn't make a difference.

I really appreciate your feedback thus far.

John

Try

Please Login or Register  to view this content.

Wow! this seems to have fixed it!! Thank You so much!

(I've also added Application.Calculation = xlCalculationAutomatic at the end of the code).