I have a template workbook that has a drop down list for the user to choose a Customer Name. I then have a function [=IFERROR(VLOOKUP(J13,Customer Data!A2:C202,3,FALSE),"")] looks at cell J13 and returns the Customers Street Address on an exact match. I have the validation error turned off so the user can type in a name that may not be in the drop down list.
My questions is because the function will wipes itself out if the user enters a name not in the list. I'd like to be able to re-insert the function when the template is closed so the next time it opens the function is there.
The Private Sub Workbook_BeforeClose(Cancel As Boolean) looks like this:
Private Sub Workbook_BeforeClose(Cancel As Boolean) If Date = Range("O4").Value Then Range("O4").Copy Range("P4") End If Range("B41:L43").ClearContents Range("C33:C35").ClearContents Range("B22:M28").ClearContents Range("J12:M15").ClearContents Range("J14").Select Range("J14").Formula = "=IFERROR(VLOOKUP(J13,Sheet2!A2:C202,3,FALSE),"")" Range("C9:D9").ClearContents Range("A1").Activate Range("C9").Activate Application.DisplayAlerts = False With Application .WindowState = xlMaximized End With Range("O7").Value = "run" ActiveWorkbook.SaveAs ("\\server1\share\QuickBooks Common\Templates\BOL_New.xltm"), FileFormat:=53 End Sub
I hope I've posted this correctly, so if not I apologize in advance.