Unload Me does not work???

[TAPS_MikeDion]

Hi everybody,
Has anyone run into a problem where Unload Me doesn't work? I have a WHOLE lot of code, so I'm hoping to figure this out without having to swamp everyone with a ton of code.

Here's the pieces...

In Userform_Initialize:

 If Not zMatch Then
     Application.ScreenUpdating = True
     Unload Me
 End If

zMatch does = False, so it proceeds to the Unload Me line.

It then jumps from the Unload Me line of code to UserForm_QueryClose, which simply calls the CLOSE button code:

Call CommandButton1_Click

Call CommandButton1_Click is the only line of code in UserForm_QueryClose.

In CommandButton1_Click, the very first line is:

If Initializing Then Exit Sub

Initializing is True, so it exits CommandButton1_Click and returns to Userform_Initialize. However, after it returns it continues on to the next line of code (which is End If) instead of unloading the form. It then continues through the remainder of the Userform_Initialize code.

Any ideas???

Thanks!

-Mike

 

[Richard Schollar]

Hey Mike

Does it work if you include an Exit Sub line?

If Not zMatch Then
     Application.ScreenUpdating = True
     Unload Me
     Exit Sub
 End If

 

[mikerickson]

"Initializing is True, so it exits CommandButton1_Click and returns to Userform_Initialize."

Shouldn't it go to Userform_QueryClose before going back to Intialize?

Is Cancel being set to True in QueryClose? If so, that's the problem.

 

[TAPS_MikeDion]

Oops, sorry Mike, yes it does return to Userform_QueryClose first, then it returns to Initialize. I haven't checked to see if Cancel is being set to True yet...I'll check that in a sec.

Richard, Exit Sub did nothing.

Thanks for the assist guys!

 

[TAPS_MikeDion]

Mike, Throwing in MsgBox Cancel after the Call CommandButton1_Click line gives me a 0.

 

[Richard Schollar]

Mike can you post up the QueryClose code so we can review it?

 

[TAPS_MikeDion]

If you insist

Private Sub UserForm_Initialize()
 
    Application.ScreenUpdating = False
 
    Dim LastZIPRow, rZIPs, i As Long, ChkZIPs
 
    Initializing = True
 
    With Sheets("Insertion Orders")
 
        n2 = .Range("A" & Rows.Count).End(xlUp).Row
 
        If n2 < 2 Then n2 = 2
 
        For i = 2 To n2
 
            If SelectedAd = .Range("A" & i).Value Then
                [COLOR=magenta]'Load Insertion Order information[/COLOR]
                WhichAdv = i
                [COLOR=magenta]'Upper Level Labels[/COLOR]
                IOAdvLabel.Caption = .Range("A" & WhichAdv).Value
                IOInsDateLabel.Caption = .Range("B" & WhichAdv).Value
                IOTotalBox.Value = .Range("C" & WhichAdv).Value
                IOInPaperBox.Value = .Range("D" & WhichAdv).Value
                IOMailBox.Value = .Range("E" & WhichAdv).Value
                IOAltDelBox.Value = .Range("F" & WhichAdv).Value
                IOSizeBox.Value = .Range("G" & WhichAdv).Value
                IOPaperBox.Value = .Range("H" & WhichAdv).Value
                IOInkBox.Value = .Range("I" & WhichAdv).Value
 
                [COLOR=magenta]'Lower Level Labels[/COLOR]
                IOAccNumBox.Value = .Range("J" & WhichAdv).Value
                IOAccNameLabel.Caption = .Range("K" & WhichAdv).Value
                IOTagLineBox.Value = .Range("L" & WhichAdv).Value
                IODateStartBox.Value = .Range("M" & WhichAdv).Value
                IOStartDateBox.Value = .Range("N" & WhichAdv).Value
                IOAgencyBox.Value = .Range("O" & WhichAdv).Value
                IOfChargeBox.Value = .Range("P" & WhichAdv).Value
                IOTypeBox.Value = .Range("Q" & WhichAdv).Value
                IORepBox.Value = .Range("R" & WhichAdv).Value
 
                [COLOR=magenta]'Lower Level Row 1[/COLOR]
                IOPubBox1.Value = .Range("S" & WhichAdv).Value
                IOAdvTypeBox1.Value = .Range("T" & WhichAdv).Value
                IOProductBox1.Value = .Range("U" & WhichAdv).Value
                IOZoneBox1.Value = .Range("V" & WhichAdv).Value
                IOLev1Box1.Value = .Range("W" & WhichAdv).Value
                IOLev2Box1.Value = .Range("X" & WhichAdv).Value
                IOCopiesBox1.Value = .Range("Y" & WhichAdv).Value
                IOTermsBox1.Value = .Range("Z" & WhichAdv).Value
                IOIONumBox1.Value = .Range("AA" & WhichAdv).Value
 
                [COLOR=magenta]'Lower Level Row 2[/COLOR]
                IOPubBox2.Value = .Range("AB" & WhichAdv).Value
                IOAdvTypeBox2.Value = .Range("AC" & WhichAdv).Value
                IOProductBox2.Value = .Range("AD" & WhichAdv).Value
                IOZoneBox2.Value = .Range("AE" & WhichAdv).Value
                IOLev1Box2.Value = .Range("AF" & WhichAdv).Value
                IOLev2Box2.Value = .Range("AG" & WhichAdv).Value
                IOCopiesBox2.Value = .Range("AH" & WhichAdv).Value
                IOTermsBox2.Value = .Range("AI" & WhichAdv).Value
                IOIONumBox2.Value = .Range("AJ" & WhichAdv).Value
 
               [COLOR=magenta]'Lower Level Row 3[/COLOR]
                IOPubBox3.Value = .Range("AK" & WhichAdv).Value
                IOAdvTypeBox3.Value = .Range("AL" & WhichAdv).Value
                IOProductBox3.Value = .Range("AM" & WhichAdv).Value
                IOZoneBox3.Value = .Range("AN" & WhichAdv).Value
                IOLev1Box3.Value = .Range("AO" & WhichAdv).Value
                IOLev2Box3.Value = .Range("AP" & WhichAdv).Value
                IOCopiesBox3.Value = .Range("AQ" & WhichAdv).Value
                IOTermsBox3.Value = .Range("AR" & WhichAdv).Value
                IOIONumBox3.Value = .Range("AS" & WhichAdv).Value
 
                [COLOR=magenta]'Notes TextBox data[/COLOR]
                IONotesBox.Value = .Range("AT" & WhichAdv).Value
                Exit For
            Else
                WhichAdv = n2
                IOAdvLabel.Caption = SelectedAd
                IOAccNameLabel.Caption = SelectedAd
                IOInsDateLabel.Caption = Sheets("Data Input").Range("A3")
                IOTotalBox.Value = Scheduler.ComboBox2.Value
                IOAccNumBox.Value = Scheduler.ComboBox3.Value
            End If
 
        Next i
    End With
 
    [COLOR=magenta]'Populate County/Location ListBox[/COLOR]
    ListBox1.Clear
    ListBox1.AddItem "Hampden"
    ListBox1.AddItem "Hampshire"
    ListBox1.AddItem "Franklin"
    ListBox1.AddItem "Connecticut"
    ListBox1.AddItem "Worcester"
    ListBox1.AddItem "Berkshire"
 
   [COLOR=magenta]'Populate ZIP Code and Newstand ListBox[/COLOR]
    ListBox2.Clear
 
    With Sheets("ZIP Codes")
        For ChkZIPs = 1 To .Cells(4, Columns.Count).End(xlToLeft).Column
           [COLOR=magenta]'Check for any ZIP Codes in the ZIP Code sheet[/COLOR]
            If Val(.Cells(4, ChkZIPs).Value) > 0 Then
                zMatch = True
                [COLOR=magenta]'Set ZIP to column letter that contains ZIP Code[/COLOR]
                ZIP = Replace(Cells(1, ChkZIPs).Address(0, 0), 1, "")
                [COLOR=magenta]'Set City to column letter after the one that contains ZIP Code[/COLOR]
                City = Replace(Cells(1, ChkZIPs + 1).Address(0, 0), 1, "")
                [COLOR=magenta]'Set County to location name in row 1[/COLOR]
                County = Split(.Cells(1, ChkZIPs).Value, " ")(0)
                Exit For
            End If
        Next ChkZIPs
    End With
 
    If Not zMatch Then
        Application.ScreenUpdating = True
        Unload Me
        Exit Sub
    End If

 

[Richard Schollar]

I did ask for the BeforeClose code

 

[TAPS_MikeDion]

It leaves the code at Unload Me and goes to:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
 
    Call CommandButton1_Click
 
End Sub

 

[Richard Schollar]

That's all that's behind QueryClose? What's in the commandbutton click code?