Activate workbook without direct reference?!
Let me explain. I am making a template file which will be reproduced many times. It relies upon a single data sheet and a bunch of central macros. All is well until I need to do a lookup in the single data sheet and return it to the template. I have to use the 'find' method for the lookup which, as i understand, means that I need to activate the data sheet. So far so good. So I get the value I want and now need to write it to the template file. But how? If i do workbooks("template.xls").activate, this works only for the template. As soon as the user saves it as something else it ceases to function.
I can write the full template path to a cell (i.e. it changes when filename changes) and use that. However, at some point I need to define that cell as a variable which means I have to refer to activeworkbook. By the time I get to using that variable, I am now in the data sheet and things fail - presumably because the macro looks up that currently active workbook, not the one I actually want.
That maybe sounds confusing but I suspect this is a problem easily sorted (afterall, templates are used everywhere!). Many thanks for the help.
Last edited by talksalot81; 04-05-2010 at 11:37 AM.
Re: Activate workbook without direct reference?!
Responding to my own question with an answer with which I am unhappy...
What I want to do is to make the 'data sheet/book' inactive (or the template active). I guess I could do this by actually closing the data sheet. This is all well and good but I am going to then need it opened again almost immediately because it is needed to populate comboboxes. Is there any other way of sending a workbook to the bottom of the pile (send to back in MS terminology)? Does hiding a sheet make it inactive?
Sorry, I have it now. I have the data list open and all I have to do is access it without activating it. Then the problem disappears. I would delete the thread but dont see I can do that!
Last edited by talksalot81; 04-05-2010 at 11:36 AM.
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
Search Engine Friendly URLs by vBSEO 3.6.0 RC 1