Error in Unloading Userform

[Endlesscroc]

Hey all,

First time posting so apologies if I get anything wrong!

I'm doing up a macro for a simple invoicing process and have everything working hunky dory. The process might be a little terrible as I'm not exactly a professional at the coding and this is something I suggested I could do myself.

I have included ALL of the code relevant to this part of the process though feel the error is at the bottom in the Msgbox asking if you would like to use this information again (This is designed to leave the form populated except for the amount).

The issue is that if (and ONLY if) the file 2014 Invoice database is already open before the macro is run then when you click "No" the userform gets initialized and not unloaded.

Private Sub OKCommandButton_Click()


Dim emptyRow As Long
Dim CustomJob As String
Dim Response As VbMsgBoxResult
Dim Databaseopen As Long


ScreenUpdating = False


    Databaseopen = 0


    If AmountTextBox.Text = "" Then
     MsgBox "No amount for invoice entered. Please enter a valid amount.", vbCritical + vbOKOnly, "Error"
     Exit Sub
    End If
 
    If Len(DateTextBox.Text) <> 10 Then
     MsgBox "Full date not entered. Please correct.", vbCritical + vbOKOnly, "Error"
     DateTextBox.Text = ""
     Exit Sub
    End If
    
    If ClientNameTextBox.Text = "" Then
     MsgBox "No client name entered. Please correct.", vbCritical + vbOKOnly, "Error"
     Exit Sub
    End If
    
    If RaisedByTextBox.Text = "" Then
     MsgBox "No name entered for ""Invoice Raised By"". Please enter your name.", vbCritical + vbOKOnly, "Error"
     Exit Sub
    End If
    
    If AddressTextBox1.Text = "" Then
     MsgBox "No address entered. Please enter at least one line of the address.", vbCritical + vbOKOnly, "Error"
     Exit Sub
    End If
    
    CustomJob = ServicesListBox.Value
    If ServicesListBox.Value = "Custom" Then
     CustomJob = Application.InputBox("Please Enter the description of the work completed in the form of Fee ""for...""", "Custom Job", , , , , Type:=2)
    End If




'Open the invoice database and save the relevant pieces of information into it




    If WorkbookExists("2014 Invoice Database.xlsm") = True Then
    Databaseopen = 1
    End If


    Application.Workbooks.Open FileName:="J:\\Admin\Invoicing\2014 Invoice Database.xlsm", Password:="Slaney"
    Workbooks("2014 Invoice Database").Activate
    Sheets("Invoices Raised").Activate




'Determine Empty Row
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1


'Transfer Information
Cells(emptyRow, 1).Value = ClientNameTextBox.Value
Cells(emptyRow, 2).Value = DateTextBox.Value
Cells(emptyRow, 3).Value = "Unpaid"
Cells(emptyRow, 4).Value = AddressTextBox1
Cells(emptyRow, 5).Value = AddressTextBox2
Cells(emptyRow, 6).Value = AddressTextBox3
Cells(emptyRow, 7).Value = AddressTextBox4
Cells(emptyRow, 8).Value = CDec(AmountTextBox)
If VATOptionButton1.Value = True Then
    Cells(emptyRow, 9).Value = CDec(Cells(emptyRow, 8).Value) * 0.23 + Cells(emptyRow, 8).Value
Else
    Cells(emptyRow, 9).Value = "0" + Cells(emptyRow, 8).Value
End If


Cells(emptyRow, 10).Value = CustomJob




If Len(Str(WorksheetFunction.CountA(Range("A:A")) - 1)) = 2 Then _
Cells(emptyRow, 11) = "2014-000" & WorksheetFunction.CountA(Range("J:J")) - 1


If Len(Str(WorksheetFunction.CountA(Range("A:A")) - 1)) = 3 Then _
Cells(emptyRow, 11) = "2014-00" & WorksheetFunction.CountA(Range("J:J")) - 1


If Len(Str(WorksheetFunction.CountA(Range("A:A")) - 1)) = 4 Then _
Cells(emptyRow, 11) = "2014-0" & WorksheetFunction.CountA(Range("J:J")) - 1


If Len(Str(WorksheetFunction.CountA(Range("A:A")) - 1)) = 5 Then _
Cells(emptyRow, 11) = "2014-" & WorksheetFunction.CountA(Range("J:J")) - 1


Cells(emptyRow, 12).Value = RaisedByTextBox
Cells(emptyRow, 13).Value = "No"
Cells(emptyRow, 14).Value = ReferenceTextBox


ActiveWorkbook.Save






'Open Invoice template and populate it with relevant information


Application.Workbooks.Open FileName:="J:\\Admin\Invoicing\Development\Sample Invoice (Including VAT).xlsx"


Cells(7, 1).Value = ClientNameTextBox
Cells(8, 1).Value = AddressTextBox1
Cells(9, 1).Value = AddressTextBox2
Cells(10, 1).Value = AddressTextBox3
Cells(11, 1).Value = AddressTextBox4
Cells(7, 8).NumberFormat = "dd mmmm yyyy"
Cells(7, 8).Value = DateTextBox.Value
Cells(8, 8).Value = Workbooks("2014 Invoice Database.xlsm").Sheets("Invoices Raised").Cells(emptyRow, 11).Value


If ReferenceTextBox.Value = "" Then
    Cells(9, 4).Value = ""
    Cells(9, 8).Value = ""
    Else: Cells(9, 8).Value = ReferenceTextBox.Value
End If


If CustomJob = "Corporation Tax" Then
    Cells(18, 1).Value = "Corporation tax compliance fee"
    Else: Cells(18, 1).Value = CustomJob
End If


Cells(18, 8).Value = CDec(AmountTextBox.Value)


If Workbooks("2014 Invoice Database.xlsm").Sheets("Invoices Raised").Cells(emptyRow, 9).Value = Workbooks("2014 Invoice Database.xlsm").Sheets("Invoices Raised").Cells(emptyRow, 8).Value Then
    Cells(22, 1).Value = ""
    Cells(22, 8).Value = ""
    Cells(27, 8).Value = Cells(18, 8).Value
    Else: Cells(22, 8).Value = CDec(AmountTextBox.Value) * 0.23
    Cells(27, 8).Value = Cells(22, 8).Value + Cells(18, 8).Value
End If


If BankOptionButton1 = True Then
    Cells(39, 1).Value = "Bank:                      AIB Morehampton"
    Cells(40, 1).Value = "Account No:           05947065"
    Cells(41, 1).Value = "Sort Code:               93-10-39"
    Cells(42, 1).Value = "IBAN:                     IE26AIBK93103905947065"
    Cells(43, 1).Value = "BIC:                        AIBKIE2D"




Else
    Cells(39, 1).Value = "Bank:                      Bank of Ireland College Green"
    Cells(40, 1).Value = "Account No:           63834731"
    Cells(41, 1).Value = "Sort Code:               90-00-17"
    Cells(42, 1).Value = "IBAN:                     IE27BOFI90001763834731"
    Cells(43, 1).Value = "BIC:                        BOFIIE2D"
End If






'Save populated invoice as "Date Name Invoice Number"
ActiveWorkbook.SaveAs FileName:="J:\\Admin\Invoicing\Invoices\" & DateTextBox & " " & ClientNameTextBox.Value & " " & Workbooks("2014 Invoice Database.xlsm").Sheets("Invoices Raised").Cells(emptyRow, 11).Value, FileFormat:= _
51, CreateBackup:=False


If Databaseopen = 0 Then
Workbooks("2014 Invoice Database").Activate
ActiveWorkbook.Save
ActiveWorkbook.Close
End If




Response = MsgBox("Would you like to use this information again?", _
vbQuestion + vbYesNo, _
"Repeat Invoice")


Select Case Response
     Case vbYes
       AmountTextBox = ""
        Exit Sub
     Case vbNo
        UnloadIt
        Exit Sub
End Select


Unload Me
End Sub

 

[RoryA]

Yep - that Open For Input method doesn't work with the new XML files (which are actually folders).