Unload form after printing in Excel

Question

In frmClothingPricer, when cmdPrint is pressed, frmPrint activates and is printed however many times asked for. I don't want 10+ frmPrint "active". How can I close frmPrint after each print loop? I have tried it on frmPrint "Unload Me" but that doesn't unload it either. ?? what am I missing?

Routine for printing

If Len(HowMany) = 0 Then 
        End
        Else
            Do Until i = HowMany
                frmPrint.Show 'prints form on activation
                i = i + 1
            Unload frmPrint 'this isn't working = several forms are open
        Loop
    End If

frmPrint code

Private Sub UserForm_Initialize()

    PrintMe

End Sub

Private Sub PrintMe()

    lblPrintMonthCode.Caption = frmClothingPricer.MonthCode
    lblPrintPricer.Caption = frmClothingPricer.Pricer
    lblPrintCost.Caption = (frmClothingPricer.Cost * 100)
    lblPrintDescription.Caption = frmClothingPricer.Description
    lblPrintPrice.Caption = frmClothingPricer.Price
    lblPrintItemNumber = frmClothingPricer.ItemNumber
    frmPrint.PrintForm
         'tried unload.me here with same results

End Sub

Do not use [class names to refer to forms](http://stackoverflow.com/a/6049062/11683), create instances explicitly. — GSerg, Nov 02 '15 at 21:59
Why would you open the form just to print and then closing the form? — Davesexcel, Nov 02 '15 at 22:11
it make tiny labels lol, found it too difficult to print out formatted the way I wanted. — instanceoftime, Nov 02 '15 at 22:14
That is because your form hasn't initialized fully. You should put the print button on the form so you can unload the form in an onclick event and then use Unload Me — Sorceri, Nov 02 '15 at 22:46
ok, smarter way for me is to have the printing loop in the frmPrint. So I am trying that now — instanceoftime, Nov 02 '15 at 22:53
something went wrong... runtime error '424' object required on my frmPrint.show?? I know this is basics 101 but I am fried — instanceoftime, Nov 02 '15 at 22:58

score 0 · Answer 1 · edited Aug 19 '17 at 11:41

I solved it by keeping all the code except labels on the original form. The latest errors revolved around variables I messed up switching around. It now works perfectly (below):

form1

Public Price As Double
Public Percent As Double
Public Cost As Currency
Public Description As String
Public MonthCode As Integer
Public Pricer As String
Public ItemNumber As Double

Private Sub UserForm_Initialize()
    Pricer = InputBox("Enter Your Pricer Number", vbOKOnly, "")
    If Len(Pricer) = 0 Then 'Checking if Length of name is 0 characters
        End
    Else
    End If
End Sub

Private Sub cmdSearch_Click()
    Dim Response As Long
    Dim NotFound As Integer
    Dim arr As Variant
    Dim i As Long
    Dim str1 As String, str2 As String, str3 As String

    lbxCost.BackColor = &H80000005
    lbxCost.Locked = False

    NotFound = 0

    ActiveWorkbook.Sheets("Items").Activate

    Response = Val("0" & Replace(txtItemNumber.Text, "-", ""))

    ItemNumber = Response

    If Response <> False Then
        With ActiveSheet
            arr = .Range("A2:D" & .Cells(.Rows.Count, "A").End(xlUp).Row)
        End With

        For i = 1 To UBound(arr)
            If arr(i, 1) = Response Then
                str1 = IIf(str1 = "", arr(i, 2), str1 & "|" & arr(i, 2))
                str2 = IIf(str2 = "", arr(i, 3), str2 & "|" & arr(i, 3))
                str3 = IIf(str3 = "", arr(i, 4), str3 & "|" & arr(i, 4))
            End If
        Next

        If str1 = "" Then
            MsgBox "Item Number Not Found!", vbExclamation
            NotFound = 1
            txtItemNumber.Text = ""
            txtItemNumber.SetFocus
        Else
            Frame1.Visible = True
            lbxDescription.List = Split(str1, "|")
            lbxCost.List = Split(str2, "|")
            ListBox3.List = Split(str3, "|")
        End If

    End If

    lbxCost.ListIndex = 0
End Sub

Private Sub lbxCost_Click()
    Frame2.Visible = True
End Sub

Private Sub lbxPercent_Click()
    Frame3.Visible = True

    lbxCost.BackColor = &H80000004
    lbxCost.Locked = True

    For x = 0 To lbxCost.ListCount - 1
        If lbxCost.Selected(x) = True Then
            Cost = lbxCost.List(x)
            Description = lbxDescription.List(x)
        End If
    Next x

    For y = 0 To lbxPercent.ListCount - 1
        If lbxPercent.Selected(y) = True Then
            Percent = lbxPercent.List(y)
        End If
    Next y

    lblPrice.Caption = (Round(Cost * (1 + (Percent / 100)), 0)) - 0.01
    Price = lblPrice.Caption
    lblItemNumber.Caption = txtItemNumber.Text
    lblDescription.Caption = Description
    MonthCode = (Year(Now)) + (Month(Now)) - 1765
    lblMonthCode.Caption = MonthCode
    lblPricer.Caption = Pricer
    cmdPrint.SetFocus
End Sub

Private Sub cmdPrint_Click()
    Dim i As Integer
    Dim Howmany As Double

    Load frmPopup

    Howmany = Val(txtQuantity.Text)

    i = 1
    Do Until i > Howmany
        frmPopup.PrintForm
        i = i + 1
    Loop

    lbxPercent.ListIndex = -1

    Frame1.Visible = False
    Frame2.Visible = False
    Frame3.Visible = False
    txtItemNumber.Text = ""

    txtItemNumber.SetFocus

    Unload frmPopup
End Sub

form2

Private Sub UserForm_Initialize()        
    lblPrintMonthCode.Caption = frmClothingPricer.MonthCode
    lblPrintPricer.Caption = frmClothingPricer.Pricer
    lblPrintCost.Caption = (frmClothingPricer.Cost * 100)
    lblPrintDescription.Caption = frmClothingPricer.Description
    lblPrintPrice.Caption = frmClothingPricer.Price
    lblPrintItemNumber = frmClothingPricer.ItemNumber
End Sub

Unload form after printing in Excel

1 Answers1