User form crashes when show method is called

twilsonco

New Member
Joined
Dec 25, 2012
Messages
33
I have a combo box on a user form, and trying to be clever I had it call another user form when a certain value is selected. But this makes Excel (2011 for Mac) crash when the show method is actually called. I can work around this quite easily by putting a command button on the main user form that does the same job of calling the secondary user form, but why should excel crash when I have a combo box call the show method for another user form?
I tried some other things before resorting to the command button, like having the combo box call a different procedure that calls the show method, But no matter what I try if the combo box's method is calling the other user form's show method Excel crashes.
i also tried having the combo box click the command button for me, but the same crash occurs.

Any ideas?
 
Last edited:

ZAX

Well-known Member
Joined
Jul 5, 2012
Messages
715
I'm sure even a Command button won't work!
The problem that the UserForm you're trying to call has a UserForm_Initialize property or something close to that which has something wrong...
PLZ check the codes of the userform you're trying to open and make sure the names and everything is fine.
ZAX
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,327
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
How are you populating the combo box and what exact event and code are you using with the combo box?
 

twilsonco

New Member
Joined
Dec 25, 2012
Messages
33
@ ZAX,
through semi-extensive testing, it works when the secondary userform is called by the command button (excel doesn't crash) and doesn't when called by the combo box (crashes). The difference is plain to see!

@ RoryA
I populate the combo box based on worksheet names using the following code. eventSelectSheet is the combobox.

Code:
Public Sub eventSheetSelectUpdate()


      With eventSheetSelect        .Clear


        For Each sheet In Worksheets
            If sheet.Index <> 1 Then
                .AddItem sheet.Name
            End If
        Next sheet
        
        .AddItem "New Sheet..."
        
        .ListIndex = .listCount - 2
    End With
    
    Call eventSheetSelect_Change


End Sub
Then I use the below code when a user selects something on the combo box. If they select the last value, "New Sheet..." then the form to create a new sheet is called.

Code:
Private Sub eventSheetSelect_Change()
    With eventSheetSelect
        If .Value = "New Sheet..." Then
            .Clear
            eventNewSheetForm.UserForm_Initialize
            eventNewSheetForm.Show
        Else
            For Each sheet In Worksheets
                If sheet.Name = .Value Then Set currentSheet = sheet
            Next sheet
            Call eventListPopulate
        End If
    End With
End Sub
eventNewSheetForm's UserForm_Initialize only sets some initial values; doesn't do anything to any other form or object. Playing around, I've tried removing everything except the .Show call (so that eventNewSheetForm doesn't initialize), and still excel crashes. But if I call .Show from a commandbutton then it works fine.

It's a simple workaround, but I felt so much more clever doing it this way :)

*Also, if the user selects one of the other values in the combobox, everything works as selected, it's only when "New Sheet..." is selected and .Show is called that it fails.
And I should note that excel crashes after eventNewSheetForm loads and becomes visible.

(and why do all my posts have code jibberish after them...)
 
Last edited by a moderator:

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,327
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
You have all kinds of recursion going on there - whenever you clear the combobox you trigger its change event, which then clears it which then triggers change...

You should also not call the Initialize event of the form (which shouldn't be public anyway) - that will happen automatically when it is loaded.

I don't know where that gibberish is coming from - are you pasting in from somewhere?
 

twilsonco

New Member
Joined
Dec 25, 2012
Messages
33
The jibberish happens if I post using quick reply or regular, and I don't see it when I'm typing, just appears in the actual post.

Edit: I'm an idiot. The clear triggers the change event, right. The code I posted above one after a few attempts at getting the crash to stop (by changing things randomly). The original code didn't have a clear in the change event, so the only clear command was in the Update procedure.

As for the recursion, I should have explained. It's not as backwards as it looks. The Update sub populates the listbox. Update is only called upon first run of the userform and after a new sheet is created. It calls the Change event after population so that the worksheet that ends up being selected (always the second to last, next to "New Sheet...") can be set to the working sheet variable. From here the eventListPopulate sub just populates a listbox on the main userform, so it has nothing to do with the combo box, so there's no recursion. Update -> Change to activate sheet and populate a listbox, and that's as complicated as it gets.

As for explicitly calling initialize methods; I was taught not to, but when actually using a form I'd like it to reinitialize each time the form is shown, but it would only initialize the first time it's called so that if you make some changes (as the user) and hide it and show it again, the changes remain. I found that explicitly calling initialize would fix that. Another option is just to make your own method that does everything initialize should do and call that instead.

But back to the combobox; with no recursion happening, it is (should be) two possibilities. The user makes a change and if the selection is "New Sheet..." then the new sheet form is opened, and if the user selects on of the other sheets then the currentSheet variable is set to the selected sheet. Seems simple enough right? Why should it crash like that?





******** type="cosymantecnisbfw" cotype="cs" id="SILOBFWOBJECTID" style="width: 0px; height: 0px; display: block;"></object>
 
Last edited:

Forum statistics

Threads
1,081,800
Messages
5,361,382
Members
400,629
Latest member
ganeshkhatri

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top