Storing and retreiving text from a separate workbook

[Benjamin92]

Hi All,

First off, thank you in advance for your help. I’m working on a project for work and I’ve been stuck on this “issue” for the past few weeks so your input is much appreciated because at this point I’m stumped.

My workbook:
I have a sheet in my workbook that contains 50 graphs with a text box beneath each graph (50 text boxes in total). The graphs show performance metrics for stores, and the text box contains notes. The idea is for a district manager to visit one of his/her stores, sit down with the manager to review the performance metrics (aka graphs), and then enter his or her action plan into the text box. The next time the district manager visits the store, he/she can sit down with the manager, pull up the previously entered action plan, and use it to check progress.

My problem:
My current set up for this involves a heavy amount of VBA, which is taking up too much space and causing the document to crash frequently. I currently have an edit button, which returns all previously saved notes and allows the user to enter new notes. With the notes pane open, a save button takes all of the notes (previously entered and new) and transfers them to the separate file. I’m using this system for each of the 50 notes panes, and it’s simply taking up too much space. Do you have any recommendations for how I can keep the functionality of my workbook while reducing the file size?

Thank you,
Benjamin

[MarvinP]

Hi Benjamin and welcome to the forum,

50 graphs is a lot. My first thought is you need to have a Pivot Table with a filter/by/store button or slicer to reduce the number of graphs.

My second thought was, Word is what you should be using for notes and not Excel. Perhaps a word doc for each store's manager to keep notes in and then a HyperLink in Excel pointing to the word doc?
Then I wondered if a hyperlink from excel to word could point to a paragraph in a doc..??
https://support.office.com/en-us/art...rs=en-US&ad=US
claims that a bookmark in a word document makes this possible.

These are my first thoughts but a sample of your workbook would give me a much better idea if my answer is even close to what you need.

[Benjamin92]

Hi MarvinP,

I attached a screenshot of the part of the doc. I'm referring to. I hope it helps do a better job of explaining what I'm trying to do. As you can see, below each graph is a text box. When clicking the "edit" button, the text box opens. With the text box open, clicking done should transfer all text to another file. Then, when reopening the text box, I want it to pull all previously entered text from the file where it's saved. Does that make more sense now? Hope this helps clarify and again, the help is much appreciate.

Excel_Screenshot_Help (2).png

Excel_Screenshot_Help (4).png