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

 

[Endlesscroc]

Apologies, my sub for UnloadIt is simply


Sub UnloadIt()
Unload RaiseInvoiceUserForm
End Sub

 

[RoryA]

Why do you reopen the workbook even if it's already open? Why not use:

    If WorkbookExists("2014 Invoice Database.xlsm") = True Then
    Databaseopen = 1
Else
    Application.Workbooks.Open FileName:="J:\\Admin\Invoicing\2014 Invoice Database.xlsm", Password:="Slaney"
End If
    Workbooks("2014 Invoice Database").Activate
    Sheets("Invoices Raised").Activate

Also, is this code in the RaiseInvoiceUserForm form?

 

[Endlesscroc]

Firstly, thanks for the quick reply and yes all the code is in the Userform.

Secondly, it is a passworded file the database and one which only myself, and the managers have access to. The idea is that if it is already open that it is assumed that they have access to the database file and that they would like for it to remain open, in case hey need to edit it etc. If it is not already opened it is assumed that they don't have access to it so it should then be closed to prevent them seeing anything they shouldn't.

You are correct about the reopening of the workbook and I've since amended that but am still getting the same issue with the userform being reinitialized and not unloaded :/

 

[RoryA]

If the code is in the userform I'm not really sure why you call a separate sub to unload it. I'd change the last part to just:

If Response = vbYes Then
       AmountTextBox = ""
        Exit Sub
End If

Unload Me
End Sub

 

[Endlesscroc]

I did have it like that but changed it as I had made the code unloadIt for a separate process.

 

[RoryA]

But you still have an Unload Me line there anyway so I see no point to it. (I don't really see a point to it anyway, to be honest)

 

[Endlesscroc]

I'm sorry I don't follow. Are you asking why I'm trying to unload it? You don't see a point in unloading it? Well I'm trying to remove the Userform off the screen if the same information is not required so it all seems smoother.

I understand that there are two unload me lines. the latter was originally the before i put in the message box to unload the form once the invoice had been created.
The former went in when I was told there should be an option to keep all data that has been input into the userform to create multiple invoices for the same person as this is quite common.

Hope that clears things up though even with removing one or both of those "unload me" 's I'm unable to get the userform to unload when the spreadhseet "2014.." is is already open.

 

[Endlesscroc]

Upon closer inspection if I run the macro and click cancel, which is linked to the sub below, it is re initializing.

Private Sub CancelCommandButton_Click()


Unload Me


End Sub

 

[Endlesscroc]

Thanks for your time but I think I have found the error. It was in fact in a different piece of code which I had put together to check if the document was in fact open by another user. I've updated it now and seems to be working!

Thanks again!

Private Sub RoundedRectangle1_Click()
    
    Dim Ret
    
    On Error GoTo ErrorHandler
    Workbooks("2014 Invoice Database").Save
    RaiseInvoiceUserForm.Show
    
    
    
ErrorHandler:
    Ret = IsWorkBookOpen("J:\\Admin\Invoicing\2014 Invoice Database.xlsm")


    If Ret = True And WorkbookExists("2014 Invoice Database.xlsm") = False Then
        MsgBox "Caution. Invoice Database currently opened by another user. Unable to save invoices.", vbCritical + vbOKOnly
    Else
        RaiseInvoiceUserForm.Show


    
    End If


End Sub


Function IsWorkBookOpen(FileName As String)
    Dim ff As Long, ErrNo As Long


    On Error Resume Next
    ff = FreeFile()
    Open FileName For Input Lock Read As #ff
    Close ff
    ErrNo = Err
    On Error GoTo 0


    Select Case ErrNo
    Case 0:    IsWorkBookOpen = False
    Case 70:   IsWorkBookOpen = True
    Case Else: Error ErrNo
    End Select




End Function


    Public Function WorkbookExists(WorkbookName As String) As Boolean
    On Error Resume Next
    If Application.Workbooks(WorkbookName) Is Nothing Then
        WorkbookExists = False
    Else
        WorkbookExists = True
    End If
    End Function