Userform Becomes Inactive when Code Runs

[8nbrink]

Hello all,

I have a rather lengthy macro that is called by a userform. After the code runs for a few seconds, the userform disappears and does not automatically reappear (even when the code is finished). The only way to get the userform to reappear is to click in an Excel spreadsheet. (This can be any spreadsheet in any workbook, not just the one in which the macro operates.) Just before the userform disappears, the title bar at the top of the userform displays its caption followed by "(Not Responding)".

For a little background, the code adds a new workbook using the workbooks.add command. I don't know if this could contribute to the problem or not. The userform itself is also quite large; containing 12 textboxes and 25 labels dispersed accross two multipage tabs. In order to try to solve the problem I have increased the drawing buffer to 128000, though this did not help. I then decreased it to 16000, though this did not help either. My final effort was to insert the userform.repaint command in multiple locations in my code, but this had no noticeable effect.

Does anyone have a suggestion as to why this is happening and how I can correct the problem? Thanks!

 

[mikerickson]

Have you tried hiding the userform before calling the problem macro and then unhiding it?

 

[8nbrink]

Thanks for the reply mikerickson!

I inserted the following code at the beginning of the private sub that is called when the appropriae command button is clicked:

userform#.hide
call NecessarySubroutine
userform#.show

This seems to cause the macro to pause after the called macro runs, and the private sub resumes when the userform is unloaded. Also, there is a status bar at the bottom of the userform that is updated as the called macro runs, and so I would like the userform to remain visible while the macro is running. (This macro is designed to process raw data, and can take upwards of 15 minutes to run if a large volume of data has been imported.) Is there any way to keep the userform visible at all times while the macro runs? Thanks again for your help!