Forum Discussion
Names in Excel Name Manager
I have a spreadsheet ( I will call it spreadsheet "A") driven by macros that creates a new worksheet each month. I am developing a second spreadsheet I will call it spreadsheet "B") that links to it for a monthly summary. I have given all the cells names that are to be linked. The issue I am dealing with is when a new monthly tab is created on "A", I want the links from "B" to be updated to the new month. The approach I am taking is to delete all of the names in "A" and recreate them for the new month. (The reason I want to delete the older names is because over time, the Names list would grow into a very large list of obsolete names). The problem I am encountering is that when a name is deleted, a dialog box appears that the user has to acknowledge that, Yes, I want to delete this name. I want to make this process transparent to the user.
So, my question is: Can I delete a name without generating this dialog box?
1 Reply
Yes there is...
I have VBA which goes thru that deletes and adds naming...
use the code "Application.DisplayAlerts = False" at the top of the coding and on exit of the programing use "Application.DisplayAlerts = True". I expect it should also work on the "ThisWorkbook" doing something like (though it would end messages until you reset it within "Private Sub Workbook_BeforeClose(Cancel As Boolean)" or "Private Sub Workbook_Deactivate()" or simuliar.)
Private Sub Workbook_Activate()
Application.DisplayAlerts = False
end sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.DisplayAlerts = True
end sub
