Where to store lookup table

[Greg Snidow]

Greetings folks. I have a lookup table stored in a workbook, that has three
columns, and about 200 rows. I use it in macros in about 10 other workbooks
that I send out to people. The problem is that with every workbook I send
out, I need to insert the lookup worksheet into the workbook in question, run
the macros, then delete it. Is there a way I could store the values of the
lookup table in Excel, so it would be available to every new work book I
open? I have absolutely no idea if this is possible, or how to do it if it
is. Thank you.

 

[Don Guillett]

How about using SELECT CASE in your macro. Then you don't need a table.

 

[Greg Snidow]

Don, thanks for the quick reply. I thought about that, but then wouldn't I
need to put the long case statement into every macro I have, and into every
new one? I was looking to make it available as an object.

 

[Rick Rothstein]

Without seeing your lookup table (how large is it?) or the macros that
operate on it, it is kind of hard to give you specific advice. Perhaps you
can attach a Module to your worksheet with a subroutine in it that inserts a
worksheet, creates the table from stored data and runs the macros (as
subroutines from the module) to do whatever it is they do and then delete
the inserted worksheet. I would think you could do this from the worksheet
open event and you could probably set a flag somewhere (an always empty
cell) to see if this was all run before (assuming your code only has to be
run once per workbook) so you can control whether it gets run or not.

 

[Don Guillett]

Try this idea

Public mv 'place at the top of the module. Change the mv in your sub and
run it.

Sub checkselectem1()
mv = 1
selectem
End Sub

Sub checkselectem2()
mv = 2
selectem
End Sub

Sub selectem()
Select Case mv
Case 1: x = 1
Case 2: x = 2
Case Else
End Select
MsgBox x
End Sub

 

[Don Guillett]

Change to this instead
Public mv
Public x
'===========
Sub checkselectem1()
mv = 1
selectem
MsgBox x
End Sub

Sub checkselectem2()
mv = 2
selectem
MsgBox x
End Sub

Sub selectem()
Select Case mv
Case 1: x = 1
Case 2: x = 2
Case Else
End Select
'MsgBox x
End Sub

 

[Greg Snidow]

Thanks Rick, that is actually an idea I was working on when I thought there
might be a better way.

 

[Greg Snidow]

Don, please forgive me, but I am just now starting to learn about more comlex
code. I do not know what you mean by 'Public mv' and 'Public x', nor what
the below code is doing. Could you mabe explain a bit further, possibly in
laymen's terms? Thank you.

 

[Don Guillett]

I made the example pretty simple. Just paste all lines that I sent into a
module and then fire one of the
checkselectem macros and you will see what is happening.
Contact me privately with your workbook if necessary.

 

[Greg Snidow]

Don, thank you so much for the offer, and I will definitely take you up on
it. I tried your code, and it returned pop ups with values. I'm not sure
what it is doing, so I will spend some more time trying to understand it,
then send you what I am trying to do. Thanks again.

Greg