User form crashes when show method is called

[twilsonco]

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?

 

[ZAX]

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]

How are you populating the combo box and what exact event and code are you using with the combo box?

 

[twilsonco]

@ 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.

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.

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...)

 

[RoryA]

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]

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>