I am wanting to get some basic user info entered via a userform or input box
that I can store in a mdule as a constant to be called in various routines.

In particular, I am looking to get the user smtp server address once from my
user and save it as a constant so that I don't have to prompt them for it
over and over again to use the CDO email function that I 'borrowed' from Ron
DeBruin's site.

My other two choices are to hard code the const for each user or find out
some way to get the CDO code to find the smtp server on its own...

Any ideas?

(please keep in mind that I'm only half way thru "ExcelVBA for Dummies" and
running XL2003)

TIA,

Steve

You can not write to a constant. If you could it would not be constant.
Constants are defined at compile time and can not be changed in any way or
deleted at run time. What you can do is to define a global variable. My
preference is to create a new module where I put all of my global variables
and constants (named modGlobals) where I defind all of my constants and
global variables. Your user form will write to this global and the rest of
your procedures will read the value from here. The one warning that I have
for you is that in your code you need to avoid the use of End (not End Sub or
End Function) because when this line of code runs it ends everything
including clearing all globally decared variables and objects.
--
HTH...

Jim Thomlinson


"Steve E" wrote:

I am wanting to get some basic user info entered via a userform or input box
that I can store in a mdule as a constant to be called in various routines.

In particular, I am looking to get the user smtp server address once from my
user and save it as a constant so that I don't have to prompt them for it
over and over again to use the CDO email function that I 'borrowed' from Ron
DeBruin's site.

My other two choices are to hard code the const for each user or find out
some way to get the CDO code to find the smtp server on its own...

Any ideas?

(please keep in mind that I'm only half way thru "ExcelVBA for Dummies" and
running XL2003)

TIA,

Steve

Constants can't be changed in code, that is why they are called constants.
How about storing the information in an .ini file?

RBS

"Steve E" wrote in message
...
I am wanting to get some basic user info entered via a userform or input
box
that I can store in a mdule as a constant to be called in various
routines.

In particular, I am looking to get the user smtp server address once from
my
user and save it as a constant so that I don't have to prompt them for it
over and over again to use the CDO email function that I 'borrowed' from
Ron
DeBruin's site.

My other two choices are to hard code the const for each user or find out
some way to get the CDO code to find the smtp server on its own...

Any ideas?

(please keep in mind that I'm only half way thru "ExcelVBA for Dummies"
and
running XL2003)

TIA,

Steve

Jim,

Thanks! It dawned on me as I was reading your reply how to do this
conceptually... now I just have to figure out how to do it really.

Thanks for your help.

Steve

"Jim Thomlinson" wrote:

You can not write to a constant. If you could it would not be constant.
Constants are defined at compile time and can not be changed in any way or
deleted at run time. What you can do is to define a global variable. My
preference is to create a new module where I put all of my global variables
and constants (named modGlobals) where I defind all of my constants and
global variables. Your user form will write to this global and the rest of
your procedures will read the value from here. The one warning that I have
for you is that in your code you need to avoid the use of End (not End Sub or
End Function) because when this line of code runs it ends everything
including clearing all globally decared variables and objects.
--
HTH...

Jim Thomlinson


"Steve E" wrote:

I am wanting to get some basic user info entered via a userform or input box
that I can store in a mdule as a constant to be called in various routines.

In particular, I am looking to get the user smtp server address once from my
user and save it as a constant so that I don't have to prompt them for it
over and over again to use the CDO email function that I 'borrowed' from Ron
DeBruin's site.

My other two choices are to hard code the const for each user or find out
some way to get the CDO code to find the smtp server on its own...

Any ideas?

(please keep in mind that I'm only half way thru "ExcelVBA for Dummies" and
running XL2003)

TIA,

Steve

Create a module called modGlobals. In there put a variable defined as such...
Public g_strMySMTP as String 'g_str is my way of saying global string:
Now in your form deactivate add something like:
modGlobals.g_strMySMTP = TextBox1.Text
Anywhere you need the SMTP just use:
msgbox modGlobals.g_strMySMTP

And like I said do not use End as a stand alone line in your code or you
will wipe out the global variables...
--
HTH...

Jim Thomlinson


"Steve E" wrote:

Jim,

Thanks! It dawned on me as I was reading your reply how to do this
conceptually... now I just have to figure out how to do it really.

Thanks for your help.

Steve

"Jim Thomlinson" wrote:

You can not write to a constant. If you could it would not be constant.
Constants are defined at compile time and can not be changed in any way or
deleted at run time. What you can do is to define a global variable. My
preference is to create a new module where I put all of my global variables
and constants (named modGlobals) where I defind all of my constants and
global variables. Your user form will write to this global and the rest of
your procedures will read the value from here. The one warning that I have
for you is that in your code you need to avoid the use of End (not End Sub or
End Function) because when this line of code runs it ends everything
including clearing all globally decared variables and objects.
--
HTH...

Jim Thomlinson


"Steve E" wrote:

I am wanting to get some basic user info entered via a userform or input box
that I can store in a mdule as a constant to be called in various routines.

In particular, I am looking to get the user smtp server address once from my
user and save it as a constant so that I don't have to prompt them for it
over and over again to use the CDO email function that I 'borrowed' from Ron
DeBruin's site.

My other two choices are to hard code the const for each user or find out
some way to get the CDO code to find the smtp server on its own...

Any ideas?

(please keep in mind that I'm only half way thru "ExcelVBA for Dummies" and
running XL2003)

TIA,

Steve