I'm wondering if something is even possible and, if it is, how to make it happen. I'll start with my question so, if someone knows the answer right away, they don't have to read the rest of my explanation. My question is, is there a way that you can modify a worksheet in a workbook by VBA without that modification being registered as a change in the workbook. I.e. - if you saved, then the VBA code was run (from a Workbook_AfterSave() subroutine), you could still close the workbook without the save reminder popup box (and, no, I don't want to just disable the reminder popup box - I know how to do that but, for the reasons described below, I don't want to do that).
Backup: I have a workbook that has (among other worksheets) a worksheet with everyone's name, office location, department, job classification, and billing rates (the "Billing Rates" worksheet). It has another worksheet (the "PMP Budget" worksheet) that references the "Billing Rates" worksheet to find an employee's billing rate when their name is entered into the "PMP Budget" worksheet.
To make it easier to locate an employee and make sure the name entered exactly matches what is shown on the "Billing Rates" worksheet, I've used data validation and VBA to filter through all of the data on the "Billing Rates" worksheet. The user has the ability to look someone up directly by selecting their name from a dropdown list of all 200+ employees, or they can filter the list of employees by any combination of their office location, department, or job classification. Making a selection in any of those boxes will cull the employee name dropdown list so that only employees who match each of the selected options is listed. This makes it easier for the user to find the employee they want or to select an employee for their project depending on any number of those factors. Once a name is selected, any remaining filters are filled in with the appropriate data and the dropdown lists are preserved so the user can edit their selections (with the exception of the Name list - that is culled to only employees who match the selected employee's department, office, and job classification). The screenshot below should help this word salad to make a little more sense:
The complication comes from the employee's job "Classification" list. All of the dropdown lists are filled in by VBA code from the "Billing Rates" worksheet. The "Billing Rates" worksheet can be updated with the latest employee billing rates (published monthly by our accounting department as a PDF). Thus, I am limited to the job classifications that are published in the PDF (without additional work to automatically modify the job titles and/or the need to constantly update the workbook if job titles are changed, added, or deleted). My list of job classifications exceeds the 255 character data validation limits. While Excel can work with data validation lists longer than 255 characters (as long as they were added via VBA and not by using the dialog box in Excel), saving the workbook with a validation list longer than 255 characters will result in an error when you try to reopen the workbook. If you allow Excel to "repair" the error, it will strip out ALL of your data validation (ask me how I know after having to rebuild the workbook during testing).
I attempted to fix this issue by "stripping out" the job classification validation lists before the workbook is saved using a Workbook_BeforeSave() subroutine (it also checks the rest of the validation lists and alerts the user if any besides the job classification are over the character limit - for example, if they haven't selected an employee and all 200 plus employees are still listed in a dropdown box). This all works correctly - it removes all of the data validation from the "Classification" cells or alerts the user if there are other lists that exceed the 255 character limit and stops the save to allow the user to correct those issues. Then, on the back-end, a Workbook_AfterSave() subroutine is run to re-insert all of the job classification validation lists, allowing the user to continue to work seamlessly after clicking "Save".
My issue is how to save and then close the workbook. In this case, whenever the workbook is saved, the job classifications are added back in by VBA after the save, which registers as a change to the workbook. If the user then attempts to close the workbook, they will get the pop-up box asking them if they want to save the workbook since there was a change since the last save. I don't want to eliminate the pop-up box (i.e. a "silent" close) because if the user actually did make changes after hitting save, I want them to be reminded they need to save. But, I don't want to get the pop-up box if they haven't made any "real" changes (i.e. if the only change is because the VBA re-inserted the classification lists).
Thanks!
Backup: I have a workbook that has (among other worksheets) a worksheet with everyone's name, office location, department, job classification, and billing rates (the "Billing Rates" worksheet). It has another worksheet (the "PMP Budget" worksheet) that references the "Billing Rates" worksheet to find an employee's billing rate when their name is entered into the "PMP Budget" worksheet.
To make it easier to locate an employee and make sure the name entered exactly matches what is shown on the "Billing Rates" worksheet, I've used data validation and VBA to filter through all of the data on the "Billing Rates" worksheet. The user has the ability to look someone up directly by selecting their name from a dropdown list of all 200+ employees, or they can filter the list of employees by any combination of their office location, department, or job classification. Making a selection in any of those boxes will cull the employee name dropdown list so that only employees who match each of the selected options is listed. This makes it easier for the user to find the employee they want or to select an employee for their project depending on any number of those factors. Once a name is selected, any remaining filters are filled in with the appropriate data and the dropdown lists are preserved so the user can edit their selections (with the exception of the Name list - that is culled to only employees who match the selected employee's department, office, and job classification). The screenshot below should help this word salad to make a little more sense:
The complication comes from the employee's job "Classification" list. All of the dropdown lists are filled in by VBA code from the "Billing Rates" worksheet. The "Billing Rates" worksheet can be updated with the latest employee billing rates (published monthly by our accounting department as a PDF). Thus, I am limited to the job classifications that are published in the PDF (without additional work to automatically modify the job titles and/or the need to constantly update the workbook if job titles are changed, added, or deleted). My list of job classifications exceeds the 255 character data validation limits. While Excel can work with data validation lists longer than 255 characters (as long as they were added via VBA and not by using the dialog box in Excel), saving the workbook with a validation list longer than 255 characters will result in an error when you try to reopen the workbook. If you allow Excel to "repair" the error, it will strip out ALL of your data validation (ask me how I know after having to rebuild the workbook during testing).
I attempted to fix this issue by "stripping out" the job classification validation lists before the workbook is saved using a Workbook_BeforeSave() subroutine (it also checks the rest of the validation lists and alerts the user if any besides the job classification are over the character limit - for example, if they haven't selected an employee and all 200 plus employees are still listed in a dropdown box). This all works correctly - it removes all of the data validation from the "Classification" cells or alerts the user if there are other lists that exceed the 255 character limit and stops the save to allow the user to correct those issues. Then, on the back-end, a Workbook_AfterSave() subroutine is run to re-insert all of the job classification validation lists, allowing the user to continue to work seamlessly after clicking "Save".
My issue is how to save and then close the workbook. In this case, whenever the workbook is saved, the job classifications are added back in by VBA after the save, which registers as a change to the workbook. If the user then attempts to close the workbook, they will get the pop-up box asking them if they want to save the workbook since there was a change since the last save. I don't want to eliminate the pop-up box (i.e. a "silent" close) because if the user actually did make changes after hitting save, I want them to be reminded they need to save. But, I don't want to get the pop-up box if they haven't made any "real" changes (i.e. if the only change is because the VBA re-inserted the classification lists).
Thanks!
