Combobox duplicated across multiple sheets pulling data from external source - crashes

  • I have a spreadsheet that is used for invoicing customers. I created a master invoice sheet which has a searchable combobox on it. I duplicate the master invoice sheet and name the new sheet something different each time. In a month the user will create 60-90 invoices for various customers. When the new invoice sheet is created or upon activation of that sheet it writes the sheet name and file name to an external customer data file. The external customer data file only contains customer data in it and is used for the searchable combobox which pulls customer data back into the invoice.


    The original layout had the customer data in the same sheet as the invoice and it worked fine. Now that I put it externally it'll work for a while then suddenly the customer data file seems to lock up and so does the invoice sheet. This only occurs when changing the customer name in the combobox.


    I've been working on this for the past couple weeks and I'm now stuck. If anyone has any suggestions I'd really appreciate it.



    If you create a directory called "temp" on the root of the C drive and you drop the two files in there you should run fine. Once you start to test the combobox that's where it'll fail.


    Appreciate any suggestions you have.

    Files

    • Invoice.zip

      (112.02 kB, downloaded 98 times, last: )
  • HI Dave,

    It definitely does have something to do with the following code;


    Private Sub ComboBox1_Change()

    If Sheets("Master Invoice").Visible = True Then

    CopySheetAndRenameByCell

    Else

    If ComboBox1.Value = "" Then Exit Sub

    ComboBox1.ListFillRange = "'Customer Data.xlsm'!DropDownList"

    Me.ComboBox1.DropDown

    End If

    End Sub


    But I can't see the problem. Do you have any suggestions?

  • It seems to be somewhat random when it happens. I can create the first invoice (copy of the master). and it happens right away. If I make a second copy of the master and use the second one it works fine unless I try and change the search. I attached a snipit of the error code that I get.