I'm using VBA in Word 2010 to build a proposal writing tool. I'm to the point where the user has selected (by drilling into folders) a product and model to be inserted, and now I want them to specify options that go with that model. Because the options and their corresponding pricing change frequently, they live in an Excel workbook. I want them to be able to specify options in Excel, then bring those back to Word.
I'm in a non-modal userform where the user has selected the model. Then they click a "choose options" button. My Word VBA code opens (or launches) Excel and opens the pricing book and activates the correct worksheet based on the model number previously chosen in Word. Then I bring the Excel window to the front using a method borrowed from cpearson.com.
So far so good. Now the user uses Excel to select the options he wants for the chosen model. When they are finished (this is where I need help), I envision them clicking a button in the Excel ribbon to activate a macro which 1) scans the sheet and transfers the selected options and corresponding prices back to Word, 2) transfers control back to the Word application, and 3) (optionally) closes Excel.
I'm a bit foggy on how to accomplish this. Word has a handle on the Excel app, but how does Excel send variables back to Word? It's complicated by the fact that I jumped to Excel from a userform, and I want to return to the userform and let them do other stuff. Do I need some kind of "loop and watch for this global variable to change then resume what I was doing in Word" kind of routine?
Let me know if you need to see any code.