Unsing names to store input from Userforms
 

I prefer to avoid writing intermediate values to the sheet. It makes the
spreadsheet difficult to modify. I would like to store the inputs from a
userform in a name eg
ActiveWorkbook.Names.Add Name:="MyNamep", RefersToR1C1:="5"

Theoretically this means that if I want to use this value in a cell, I can
say
=MyName

The problem is that I want to use the name value to initialise the control
on my form so tha next time the form is opened, the previous selection is
set. It appears that Msgbox names("MyName").value renders a TEXT value ="5"
and not a numerical one.

I wanted to use this value in a Select Case statement, but it seems working
with a TEXT value may be difficult. Is there a way I can get a numerical
value instead?

I would also like to be able to say
NewVariable = 3.14 * names("MyName").value

Any Suggestions ??

.- -. Bye Bye
/|6 6|\ - Pavlos
{/(_0_)\}
/ ^ \_
(/_/^\_\)

Unsing names to store input from Userforms
 

Type convert the string using Val(names("MyName").value)
or CDbl(names("MyName").value). If you may have decimal
values and use ',' as separator Val will only return
numbers to the left of the decimal separator as it only
recognize '.' as such.

ojv
-----Original Message-----
I prefer to avoid writing intermediate values to the
sheet. It makes the
spreadsheet difficult to modify. I would like to store
the inputs from a
userform in a name eg
ActiveWorkbook.Names.Add Name:="MyNamep",
RefersToR1C1:="5"

Theoretically this means that if I want to use this
value in a cell, I can
say
=MyName

The problem is that I want to use the name value to
initialise the control
on my form so tha next time the form is opened, the
previous selection is
set. It appears that Msgbox names("MyName").value
renders a TEXT value ="5"
and not a numerical one.

I wanted to use this value in a Select Case statement,
but it seems working
with a TEXT value may be difficult. Is there a way I can
get a numerical
value instead?

I would also like to be able to say
NewVariable = 3.14 * names("MyName").value

Any Suggestions ??

.- -. Bye Bye
/|6 6|\ - Pavlos
{/(_0_)\}
/ ^ \_
(/_/^\_\)
.

Unsing names to store input from Userforms
 

I tried this but it just renders a zero. It seems that for the Val command to
work, you need to enter the actual string. eg
Val(" This is my 2nd attempt") renders the value 2



" wrote:

Type convert the string using Val(names("MyName").value)
or CDbl(names("MyName").value). If you may have decimal
values and use ',' as separator Val will only return
numbers to the left of the decimal separator as it only
recognize '.' as such.

ojv
-----Original Message-----
I prefer to avoid writing intermediate values to the
sheet. It makes the
spreadsheet difficult to modify. I would like to store
the inputs from a
userform in a name eg
ActiveWorkbook.Names.Add Name:="MyNamep",
RefersToR1C1:="5"

Theoretically this means that if I want to use this
value in a cell, I can
say
=MyName

The problem is that I want to use the name value to
initialise the control
on my form so tha next time the form is opened, the
previous selection is
set. It appears that Msgbox names("MyName").value
renders a TEXT value ="5"
and not a numerical one.

I wanted to use this value in a Select Case statement,
but it seems working
with a TEXT value may be difficult. Is there a way I can
get a numerical
value instead?

I would also like to be able to say
NewVariable = 3.14 * names("MyName").value

Any Suggestions ??

.- -. Bye Bye
/|6 6|\ - Pavlos
{/(_0_)\}
/ ^ \_
(/_/^\_\)
.

Unsing names to store input from Userforms
 

Sorry, use Range("MyName").Value instead of Names
("MyName").Value as the value property of Name returns
the formula the name refers to.

ojv
-----Original Message-----
I tried this but it just renders a zero. It seems that
for the Val command to
work, you need to enter the actual string. eg
Val(" This is my 2nd attempt") renders the value 2



" wrote:

Type convert the string using Val(names
("MyName").value)
or CDbl(names("MyName").value). If you may have
decimal
values and use ',' as separator Val will only return
numbers to the left of the decimal separator as it
only
recognize '.' as such.

ojv
-----Original Message-----
I prefer to avoid writing intermediate values to the
sheet. It makes the
spreadsheet difficult to modify. I would like to
store
the inputs from a
userform in a name eg
ActiveWorkbook.Names.Add Name:="MyNamep",
RefersToR1C1:="5"

Theoretically this means that if I want to use this
value in a cell, I can
say
=MyName

The problem is that I want to use the name value to
initialise the control
on my form so tha next time the form is opened, the
previous selection is
set. It appears that Msgbox names("MyName").value
renders a TEXT value ="5"
and not a numerical one.

I wanted to use this value in a Select Case
statement,
but it seems working
with a TEXT value may be difficult. Is there a way I
can
get a numerical
value instead?

I would also like to be able to say
NewVariable = 3.14 * names("MyName").value

Any Suggestions ??

.- -. Bye Bye
/|6 6|\ - Pavlos
{/(_0_)\}
/ ^ \_
(/_/^\_\)
.


.

Unsing names to store input from Userforms
 

Sorry, use Range("MyName").Value instead
That will raise an error since MyName isn't a range.

to the OP

You can use evaluate. Demonstrated from the immediate window:

? Names("Myname").Refersto
=5
? evaluate(Names("MyName").Refersto)
5
? application.IsNumber(evaluate(Names("MyName").Refe rsto))
True

Or you can parse out the equal sign and do the conversion.

Dim sStr as String, lVal as Long
sStr = Names("MyName").Refersto
lVal = cdbl(right(sStr,len(sStr)-1))

--
Regards,
Tom Ogilvy

wrote in message
...
Sorry, use Range("MyName").Value instead of Names
("MyName").Value as the value property of Name returns
the formula the name refers to.

ojv
-----Original Message-----
I tried this but it just renders a zero. It seems that
for the Val command to
work, you need to enter the actual string. eg
Val(" This is my 2nd attempt") renders the value 2



" wrote:

Type convert the string using Val(names
("MyName").value)
or CDbl(names("MyName").value). If you may have
decimal
values and use ',' as separator Val will only return
numbers to the left of the decimal separator as it
only
recognize '.' as such.

ojv
-----Original Message-----
I prefer to avoid writing intermediate values to the
sheet. It makes the
spreadsheet difficult to modify. I would like to
store
the inputs from a
userform in a name eg
ActiveWorkbook.Names.Add Name:="MyNamep",
RefersToR1C1:="5"

Theoretically this means that if I want to use this
value in a cell, I can
say
=MyName

The problem is that I want to use the name value to
initialise the control
on my form so tha next time the form is opened, the
previous selection is
set. It appears that Msgbox names("MyName").value
renders a TEXT value ="5"
and not a numerical one.

I wanted to use this value in a Select Case
statement,
but it seems working
with a TEXT value may be difficult. Is there a way I
can
get a numerical
value instead?

I would also like to be able to say
NewVariable = 3.14 * names("MyName").value

Any Suggestions ??

.- -. Bye Bye
/|6 6|\ - Pavlos
{/(_0_)\}
/ ^ \_
(/_/^\_\)
.


.