0

In my Excel spreadsheet I use the below VBA codes to get a UserForm with a ComboBox using the values from sheet1:

List for ComboBox1 on spreadsheet:

     A     B
1    Q1
2    Q2
3    Q3
4    Q4

VBA code to call UserForm1 (connected to a button on the spreadsheet):

Sub Test()
Call UserForm1.Show(vbModeless)
End Sub

VBA code of UserForm1:

Private Sub UserForm_Activate()
ComboBox1.List = Sheet1.Range("A1:A4").Value
End Sub

Private Sub CommandButton1_Click()
Unload UserForm1
End Sub

All this works fine so far. However, when the user pushes the button to load the UserForm1 the entire spreadsheet is re-calculated by Excel which takes a lot of time.

Therefore, I tried to change the code to this:

Sub Test()
Application.Calculation = xlManual
Call UserForm1.Show(vbModeless)
Application.Calculation = xlAutomatic
End Sub

With this code the UserForm1 is loaded without re-calculating the spreadsheet.
However, since I do not want to keep the spreadsheet in the "manual" re-calculating mode I have to set it back to "automatic" within the VBA code. However, this causes the same problem of the long loading time. The only difference is that the re-calculation now happens after the UserForm is displayed.

Is it possible to load the entire UserForm without re-calculating the spreadsheet but keep it in automatic calculation mode?

Community
  • 1
  • 1
Michi
  • 4,663
  • 6
  • 33
  • 83
  • 1
    [1] Try to **hide** the UserForm (better: new userform instance) instead of *unloading*. [2] (Handle QueryClose to prevent unwanted object destruction). [3] Then you can use **`UserForm_Initiate`** instead of `UserForm_Activate` to load data only once. [4] See referring themes (thx @Mathieu-Guindau): [Destroy a modeless Userform instance properly](https://stackoverflow.com/questions/47357708/vba-destroy-a-modeless-userform-instance-properly) and [UserForm1.Show?](https://rubberduckvba.wordpress.com/2017/10/25/userform1-show/) – T.M. Jun 26 '18 at 07:28
  • 2
    Is this all your (userform) code? I would say there's a writing to a cell somewhere from your Userform. I don't think loading will trigger the calculate-event – EvR Jun 26 '18 at 08:07
  • I can confirm what @EvR said - loading a simpe userform does not trigger the calculate event, I have put a `=NOW()` formula and loaded a form, it did not recalculate. – Vityata Jun 26 '18 at 08:24
  • @EvR: Sorry, you are right. This was a mistake in my question. I am using datas from the spreadsheet for the UserForm. I have edited my question. – Michi Jun 26 '18 at 08:26
  • 1
    @Michi - don't wanna be the devil's advocate, but reading from a spreadsheet does not trigger the calculate event as well... – Vityata Jun 26 '18 at 08:26
  • 1
    @Vityata: I am checking through my original file and I think - thanks to your comments - I found the reason why the loading is caused. My Excel is linked to a BI tool. The tool inserts data directly into the spreadsheet. If I eliminate the data from the BI tool there is no calculation and the UserForm loads immediately. mmmh, now I need to figure out how I can avoid the BI tool to calculate the data when the user form is loaded ... – Michi Jun 26 '18 at 08:35

0 Answers0