Update Links after Closing Linked File
I have built a budget worksheet that relies on linked detail sheets to update
the budget. In "Budget Summary.xls" it lists all account numbers. Each account number has a hyperlink (using Worksheet_FollowHyperlink) that opens the detail budget entry workbook for that account. So, if I click on "40000", then "Detail - 40000.xls" opens. "Budget Summary.xls" has links to the detail entry sheets to show the monthly budgeted amounts. These update automatically when the workbook opens. So say I open "Detail - 40000.xls" with the hyperlink. My detail workbook has two command buttons - one saves the changes, closes detail, and returns to "Budget Summary.xls" and one does not save changes, closes detail and returns to "Budget Summary.xls". Here's my problem, if a user makes changes to "Detail - 40000.xls" but decides they don't want them and clicks the second button to not save the changes, "Budget Summary.xls" retains the changes even though they weren't saved. If I refresh the links, then the original saved values will return on "Budget Summary.xls". What I would like to do is automate via VBA so that if the user clicks on the second command button in the detail file (i.e., don't save changes) then my "Budget Summary.xls" file will return to it's original state. Any help would be appreciated! -- Thanks, Gerry O. |
Update Links after Closing Linked File
I guess you could create an application event that runs each time you close a
workbook that updates links for all the existing workbooks. If you want to try, you can start by reading Chip Pearson's notes: http://www.cpearson.com/excel/AppEvent.aspx I think I'd refresh the links manually. Gerry O wrote: I have built a budget worksheet that relies on linked detail sheets to update the budget. In "Budget Summary.xls" it lists all account numbers. Each account number has a hyperlink (using Worksheet_FollowHyperlink) that opens the detail budget entry workbook for that account. So, if I click on "40000", then "Detail - 40000.xls" opens. "Budget Summary.xls" has links to the detail entry sheets to show the monthly budgeted amounts. These update automatically when the workbook opens. So say I open "Detail - 40000.xls" with the hyperlink. My detail workbook has two command buttons - one saves the changes, closes detail, and returns to "Budget Summary.xls" and one does not save changes, closes detail and returns to "Budget Summary.xls". Here's my problem, if a user makes changes to "Detail - 40000.xls" but decides they don't want them and clicks the second button to not save the changes, "Budget Summary.xls" retains the changes even though they weren't saved. If I refresh the links, then the original saved values will return on "Budget Summary.xls". What I would like to do is automate via VBA so that if the user clicks on the second command button in the detail file (i.e., don't save changes) then my "Budget Summary.xls" file will return to it's original state. Any help would be appreciated! -- Thanks, Gerry O. -- Dave Peterson |
All times are GMT +1. The time now is 08:46 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com