vba Userforms wont unload and then freeze

[Dustan]

This is long and I am sorry but I am desperate. Thanks in advance

I have a control userform with various buttons to open different userforms
In this case - I click the button to open the "Add New Account" userform - AddNewAccount userform opens via this code

Private Sub btnNewAccount_Click()
    Unload ControlPanel
    AddNewAccount.Show
End Sub

AddNewAccount
IF I select "Add New..." in the combobox, I have an exit event to close AddNewAccount and open "NewAccountOrDetailType" via this code

Private Sub cmbSelectAccountType_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim strAccountType As String


strAccountType = cmbSelectAccountType.Value


If Not strAccountType = "Add New..." Then
   GoTo EndSubRoutine
Else
    Unload Me
End If


Load NewAccountOrDetailType
NewAccountOrDetailType.chkNewAccountType.Value = True
NewAccountOrDetailType.chkNewDetailType.Value = False
NewAccountOrDetailType.lblTitle.Caption = "Enter Name for New Account"
NewAccountOrDetailType.Caption = "New Account"
NewAccountOrDetailType.btnSave.Caption = "Create New Account"
NewAccountOrDetailType.Show


EndSubRoutine:


End Sub

NewAccountOrDetailType (note - the checkbox referred to in the previous code is not visible. It determines if a new Account is being added or a new Detail is being added)
Here is the code for both buttons on the userform. The issue I am having is - If cancel is clicked on NewAccountOrDetail and then again on AddNewAccount, NewAccountorDetail is being showed again. Close it and AddNewAccountUserform freezes. Have to hard crash excel. If the create new account button is clicked, it should unload me and show Addnewaccount again but it shows NewAccountDetailType again, close it again and AddNewAccount is shown but frozen - once again, have to hard crash Excel. I am absolutely confused.

Private Sub btnCancel_Click()
Unload NewAccountOrDetailType
AddNewAccount.Show
End Sub

Private Sub btnSave_Click()
Dim lngConfigRows As Long, lngConfigNewRow As Long
Dim strAccountOrDetail As String, strName As String, strColumnLetter As String, strConfigRange As String, strComboboxName As String, msg As String
Dim wsConfig As Worksheet
Dim rngNameFound As Range
Dim cmb As MSForms.ComboBox


Set wsConfig = Sheets("Configuration")


strName = txtAccountName.Value


If strName = "" Then
    MsgBox ("Name required for new " & strAccountOrDetail & ".")
    GoTo EndSubRoutine
End If


If chkNewDetailType.Value = True Then
    strAccountOrDetail = "Detail"
Else
    strAccountOrDetail = "Account"
End If


strComboboxName = "cmbSelect" & strAccountOrDetail & "Type"


If strAccountOrDetail = "Account" Then
    strColumnLetter = "D"
ElseIf strAccountOrDetail = "Detail" Then
    strColumnLetter = "E"
End If


lngConfigRows = wsConfig.Range(strColumnLetter & "1048576").End(xlUp).Row


strConfigRange = strColumnLetter & "2:" & strColumnLetter & lngConfigRows


Set rngNameFound = wsConfig.Range(strConfigRange).Find(what:=strName, LookIn:=xlFormulas, lookat:=xlWhole)


If Not (rngNameFound Is Nothing) Then
    msg = MsgBox("This " & strAccountOrDetail & " already exists.", vbCritical, strAccountOrDetail & " already exists")
    GoTo EndSubRoutine
End If


lngConfigNewRow = lngConfigRows + 1


wsConfig.Range(strColumnLetter & lngConfigNewRow).Value = strName


strConfigRange = strColumnLetter & "2:" & strColumnLetter & lngConfigNewRow


ActiveWorkbook.Worksheets("Configuration").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Configuration").Sort.SortFields.Add Key:=Range(strColumnLetter & "1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Configuration").Sort
    .SetRange Range(strConfigRange)
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With


Unload Me


AddNewAccount.Show


EndSubRoutine:
End Sub

 

[NdNoviceHlp]

If Not strAccountType = "Add New..." Then
   GoTo EndSubRoutine
Else
    Unload AddNewAccount
End If

NewAccountOrDetailType.chkNewAccountType.Value = True
NewAccountOrDetailType.chkNewDetailType.Value = False
NewAccountOrDetailType.lblTitle.Caption = "Enter Name for New Account"
NewAccountOrDetailType.Caption = "New Account"
NewAccountOrDetailType.btnSave.Caption = "Create New Account"
NewAccountOrDetailType.Show

Be specific with unload and remove the load NewAccountOrDetailType part. HTH. Dave

 

[DanteAmor]

Each time you leave the combo, the form NewAccountOrDetailType reopens. Then when you return to the AddNewAccount form, exit the combo again and re-enter NewAccountOrDetailType. Enter a cycle without end, the memory is finished and that's why it freezes excel.


To correct, Do the following:

1. Remove your event exit.


2. Put the event change:

Private Sub cmbSelectAccountType_Change()
    If cmbSelectAccountType.Value = "Add New..." Then
        Load NewAccountOrDetailType
    '    NewAccountOrDetailType.chkNewAccountType.Value = True
    '    NewAccountOrDetailType.chkNewDetailType.Value = False
    '    NewAccountOrDetailType.lblTitle.Caption = "Enter Name for New Account"
    '    NewAccountOrDetailType.Caption = "New Account"
    '    NewAccountOrDetailType.btnSave.Caption = "Create New Account"
        NewAccountOrDetailType.Show
    End If
End Sub

3. In NewAccountOrDetailType

Private Sub btnCancel_Click()
    Unload NewAccountOrDetailType
    '
    'This line should not be, because when you showed NewAccountOrDetailType you did not close AddNewAccount and then you are loading it again.
    'AddNewAccount.Show
End Sub

Try and tell me.

 

[Dustan]

If Not strAccountType = "Add New..." Then
   GoTo EndSubRoutine
Else
    Unload AddNewAccount
End If

NewAccountOrDetailType.chkNewAccountType.Value = True
NewAccountOrDetailType.chkNewDetailType.Value = False
NewAccountOrDetailType.lblTitle.Caption = "Enter Name for New Account"
NewAccountOrDetailType.Caption = "New Account"
NewAccountOrDetailType.btnSave.Caption = "Create New Account"
NewAccountOrDetailType.Show

Be specific with unload and remove the load NewAccountOrDetailType part. HTH. Dave

Hi there Dave, Thank you but - The "Unload Me" was a last ditch attempt that didn't work. I normally always specify the form to be unloaded. I did give it another try and it did not work.

 

[Dustan]

Each time you leave the combo, the form NewAccountOrDetailType reopens. Then when you return to the AddNewAccount form, exit the combo again and re-enter NewAccountOrDetailType. Enter a cycle without end, the memory is finished and that's why it freezes excel.


To correct, Do the following:

1. Remove your event exit.


2. Put the event change:

Private Sub cmbSelectAccountType_Change()
    If cmbSelectAccountType.Value = "Add New..." Then
        Load NewAccountOrDetailType
    '    NewAccountOrDetailType.chkNewAccountType.Value = True
    '    NewAccountOrDetailType.chkNewDetailType.Value = False
    '    NewAccountOrDetailType.lblTitle.Caption = "Enter Name for New Account"
    '    NewAccountOrDetailType.Caption = "New Account"
    '    NewAccountOrDetailType.btnSave.Caption = "Create New Account"
        NewAccountOrDetailType.Show
    End If
End Sub

3. In NewAccountOrDetailType

Private Sub btnCancel_Click()
    Unload NewAccountOrDetailType
    '
    'This line should not be, because when you showed NewAccountOrDetailType you did not close AddNewAccount and then you are loading it again.
    'AddNewAccount.Show
End Sub

Try and tell me.

The reason I put an exit event is because of the autofill property of the combobox. "Add New..." is one of the list items in the combobox. For example, if I were to intend to type "Adventure", the combobox autofills the text value with each new letter addition so by the time I have typed "Ad" - it has autofilled the combobox with "Add New..." and therefore triggering the _change event and loading the NewAccountOrDetailType form. Is there a way to avoid the autofill defaulting to the "Add New..." list item?

I am also confused - In the original exit event, I do have the code to "Unload Me" or as originally coded "Unload AddNewAccount". Does this not close the AddNewAccount userform? If not, How do I go about closing the userform and removing it from memory?

I am trying your code now - will verify either way in a minute.

 

[Dustan]

Okay, so - I moved the "Add New..." list item in the combobox to the very last value. That way - it would be the last thing to autofill only if there is no other match before it. Change the code from the exit event to the change event as you recommended. This is working for now.

Thank you for your help.

 

[DanteAmor]

I recommend you use the change event.
The exit event you have to control with global variables, even when you are in the combo and you want to press a button or close the form, the first event to be executed is the exit event.



Or remove event change and put another button to call the NewAccountOrDetailType form:

Private Sub CommandButton1_Click()
    If cmbSelectAccountType.Value = "Add New..." Then
        Load NewAccountOrDetailType
    '    NewAccountOrDetailType.chkNewAccountType.Value = True
    '    NewAccountOrDetailType.chkNewDetailType.Value = False
    '    NewAccountOrDetailType.lblTitle.Caption = "Enter Name for New Account"
    '    NewAccountOrDetailType.Caption = "New Account"
    '    NewAccountOrDetailType.btnSave.Caption = "Create New Account"
        NewAccountOrDetailType.Show
    End If
End Sub

try to make it simple, so that in the future when there are changes it will be much easier

 

[DanteAmor]

Okay, so - I moved the "Add New..." list item in the combobox to the very last value. That way - it would be the last thing to autofill only if there is no other match before it. Change the code from the exit event to the change event as you recommended. This is working for now.

Thank you for your help.

I'm glad to help you