Excel VBA UserForm locking up sheet

Question

Hello and good morning,

I am new to Excel and VBA but am working on a spreadsheet that will greatly assist me in what I do. I have a button "btnQuery" that when pressed, should do a few things:

Turn off calculations
Perform 2 queries
Update pivot charts
Turn on calculations

It was great and working fine, until I decided to add in a progress bar. I'm working off an example from here: http://spreadsheetpage.com/index.php/tip/displaying_a_progress_indicator/

My intention is to make it so that the bar updates: 1. 5% before turn off calculations 2. 5% after turn off calculations 3. 35% after first query 4. 35% after second query 5. 15% after pivot updates 6. 5% after turn on calculations

However, whenever I "Show" the userform, it just locks up my page until I exit out of it. Here is the code that I have that will hopefully help:

Sub btnQuery_Click()
Userform1.Show
Call code.calcturnoff
Call code.data
Call code.pivot
Call code.calcturnon
End Sub

I currently have no code for the userform. Can anybody please offer some advice?

Thanks a million!

Rik Sportel · Accepted Answer · 2017-10-23 14:59:13Z

3

It's because you're waiting for input. Make sure you open it as a non-modal dialog:

Userform1.Show vbModeless

answered Oct 23, 2017 at 14:59

Rik Sportel

2,6781 gold badge17 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

artemis Over a year ago

This is the second time you've answered a question of mine. Thanks!

Collectives™ on Stack Overflow

Excel VBA UserForm locking up sheet

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related