Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to skip Update Values dialog when links change tonon-existent spreadsheets?
I have a VBA subroutine within an excel spreadsheet which goes through
several cells containing links to external spreadsheets and changes the formulas containing those links to point to a different spreadsheet based on the date. It works, kinda, except for the annoying popups which occur from trying to link to non-existing spreadsheets. The title shows "Update Values:" with a spreadsheet link. I was wondering if there's a way to prevent these dialogs from showing up when a link was changed in a formula to now point to a non-existent spreadsheet. The formulas contain something like "if(iserror(vlookup( referencing an external spreadsheet)), "ND", vlookup(..))". Come to think of it, is there a way to check if the spreadsheet exists before even attempting a link to it in a formula and yet still keep the formula? -- Ken Shaffer |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to skip Update Values dialog when links change tonon-existent spreadsheets?
Ken,
Try Application.DisplayAlerts = False at the start of your code, and reset to True after. For the fileexits, look at the Dur function in Help. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Ken Shaffer" wrote in message ... I have a VBA subroutine within an excel spreadsheet which goes through several cells containing links to external spreadsheets and changes the formulas containing those links to point to a different spreadsheet based on the date. It works, kinda, except for the annoying popups which occur from trying to link to non-existing spreadsheets. The title shows "Update Values:" with a spreadsheet link. I was wondering if there's a way to prevent these dialogs from showing up when a link was changed in a formula to now point to a non-existent spreadsheet. The formulas contain something like "if(iserror(vlookup( referencing an external spreadsheet)), "ND", vlookup(..))". Come to think of it, is there a way to check if the spreadsheet exists before even attempting a link to it in a formula and yet still keep the formula? -- Ken Shaffer |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to skip Update Values dialog when links change tonon-existent spreadsheets?
That worked! I knew about that, but didn't think it applied in this situation
Thanks - Ken Shaffe ----- Bob Phillips wrote: ---- Ken Tr Application.DisplayAlerts = Fals at the start of your code, and reset to True after For the fileexits, look at the Dur function in Help -- HT Bob Phillip ... looking out across Poole Harbour to the Purbeck (remove nothere from the email address if mailing direct "Ken Shaffer" wrote in messag .. I have a VBA subroutine within an excel spreadsheet which goes throug several cells containing links to external spreadsheets and changes th formulas containing those links to point to a different spreadsheet base o the date It works, kinda, except for the annoying popups which occur from trying t link to non-existing spreadsheets. The title shows "Update Values:" with spreadsheet link |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to skip Update Values dialog when links change tonon-existent spreadsheets?
Probably obvious, but in case not:
Dur should be Dir -- Regards, Tom Ogilvy "Bob Phillips" wrote in message ... Ken, Try Application.DisplayAlerts = False at the start of your code, and reset to True after. For the fileexits, look at the Dur function in Help. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Ken Shaffer" wrote in message ... I have a VBA subroutine within an excel spreadsheet which goes through several cells containing links to external spreadsheets and changes the formulas containing those links to point to a different spreadsheet based on the date. It works, kinda, except for the annoying popups which occur from trying to link to non-existing spreadsheets. The title shows "Update Values:" with a spreadsheet link. I was wondering if there's a way to prevent these dialogs from showing up when a link was changed in a formula to now point to a non-existent spreadsheet. The formulas contain something like "if(iserror(vlookup( referencing an external spreadsheet)), "ND", vlookup(..))". Come to think of it, is there a way to check if the spreadsheet exists before even attempting a link to it in a formula and yet still keep the formula? -- Ken Shaffer |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Repost: Disabling Update Links dialog box | Excel Discussion (Misc queries) | |||
Disabling Update Links dialog box | Excel Discussion (Misc queries) | |||
Avoid Update Links dialog in Excel 2003 | Links and Linking in Excel | |||
Update links box gives Continue or Edit Links dialog | Excel Discussion (Misc queries) | |||
Disable update links dialog box | Excel Programming |