Where to store lookup table
 

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.

Where to store lookup table
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Greg Snidow" wrote in message
...
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.

Where to store lookup table
 

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.

"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Greg Snidow" wrote in message
...
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.

Where to store lookup table
 

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.

--
Rick (MVP - Excel)


"Greg Snidow" wrote in message
...
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.

"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Greg Snidow" wrote in message
...
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.

Where to store lookup table
 

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
Microsoft MVP Excel
SalesAid Software

"Greg Snidow" wrote in message
...
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.

"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Greg Snidow" wrote in message
...
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.

Where to store lookup table
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
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
Microsoft MVP Excel
SalesAid Software

"Greg Snidow" wrote in message
...
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.

"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Greg Snidow" wrote in message
...
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.

Where to store lookup table
 

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

"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)


"Greg Snidow" wrote in message
...
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.

"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Greg Snidow" wrote in message
...
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.

Where to store lookup table
 

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" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
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
Microsoft MVP Excel
SalesAid Software

"Greg Snidow" wrote in message
...
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.

"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Greg Snidow" wrote in message
...
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.

Where to store lookup table
 

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.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Greg Snidow" wrote in message
...
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" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
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
Microsoft MVP Excel
SalesAid Software

"Greg Snidow" wrote in message
...
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.

"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Greg Snidow" wrote in message
...
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.

Where to store lookup table
 

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

"Don Guillett" wrote:

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.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Greg Snidow" wrote in message
...
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" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
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
Microsoft MVP Excel
SalesAid Software

"Greg Snidow" wrote in message
...
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.

"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Greg Snidow" wrote in message
...
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.