Userform ramps up CPU during initialize

[Krull_WarriorKing]

Hello,

I have created a simple Userform with text boxes, list boxes, and a combo box. I show (.show) the Userform from a module, and the first time the Userform pops up when Excel has been opened, it works lightning fast. However, just about every consecutive 'call' to the Userform from the module results in the CPU spiking to nearly 100%. If I wait to insert information into the various boxes, then the remaining code runs normally/smoothly. I have another Userform that initializes and runs smoothly every time, which makes this problem all the more frustrating. Can anyone help me to figure out why this Userform initializes so slowly?

Code from module to show form:

Sub addNewTE()
    Dim frm As New addTE
    With frm
        Set .sh = sh
        frm.Show vbModal
        If .IsCancelled Then
            sh.Activate
            Exit Sub
        End If
        Unload frm
        Set frm = Nothing
    End With

Code for Userform:

Public sh As Worksheet
Private cancelling As Boolean
Public EnableEvents As Boolean

Private Sub cancelButton_Click()
    If Me.EnableEvents = False Then Exit Sub
    cancelling = True
    Me.Hide
End Sub

Private Sub okButton_Click()
    If Me.EnableEvents = False Then Exit Sub

    'write all data to the worksheet
    Call WriteDataToSheet(sh)
    
    Call EmptyTextBoxes
    
    'close userform
    Me.Hide
    
End Sub

Public Property Get IsCancelled() As Boolean
    IsCancelled = cancelling
End Property

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If Me.EnableEvents = False Then Exit Sub
    If CloseMode = VbQueryClose.vbFormControlMenu Then
        cancelling = True
        Cancel = True
        Me.Hide
    End If
End Sub

Private Sub UserForm_Initialize()
    Me.EnableEvents = True
    
    Dim envArr As Variant
    envArr = Array("Prod", "Eng", "Dev")
    With Me.Env
        .List = envArr
        .MultiSelect = fmMultiSelectExtended
    End With
    
    Dim comboArr As Variant, hwTestedList As Variant, pgmList As Variant
    comboArr = Array("Opt1", "Opt2", "Opt3")
    Me.Owner.List = comboArr
    Me.Owner.ListIndex = 0
    
    pgmList = Array("I", "II", "III")
    With Me.Programs
        .List = pgmList
        .MultiSelect = fmMultiSelectExtended
    End With
    
    hwTestedList = Array("Opt1", "Opt2", "Opt3")
    With Me.hwTested
        .List = hwTestedList
        .MultiSelect = fmMultiSelectExtended
    End With
    
    
End Sub

Private Sub EmptyTextBoxes()
    Dim c As Control
    If Me.EnableEvents = False Then Exit Sub
    For Each c In Me.Controls
        If TypeName(c) = "TextBox" Then
            c.Value = ""
        End If
    Next
End Sub

Private Sub WriteDataToSheet(sh As Worksheet)
    Dim newRow As Long, lcol As Long, tmpstr As String, i As Long, _
        cnt As Long
    Me.EnableEvents = False
    'Lots of stuff you probably don't care about that; adds new rows with Userform info into different sheets
    Me.EnableEvents = True
End Sub

 

[Krull_WarriorKing]

I never got any replies, but was eventually able to figure out a way around the problem. I tried rewriting the userform code in various ways to ensure that no userform controls were kicked off unintentionally, tried cleaning up the userform to remove anything not absolutely necessary, etc. Additionally, I moved things around in the subfunction to load the form earlier, unload the form every time, never unload the form, etc.

What eventually worked is when I switched the Control Form button for an ActiveX button. Instead of using the button to call the addNewTE subfunction in my module (the one shown above that opens the userform), I used the ActiveX button to open the userform and then called the addNewTE sub from the userform. By opening the userform directly from the button rather than through the subfunction, I am able to avoid the crippling PCU overutilization that would occur with essentially every other run.

Hopefully this can help somebody in the future, as I'm sure I can't have been the only person to have experienced this issue.