Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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_)\} / ^ \_ (/_/^\_\) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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_)\} / ^ \_ (/_/^\_\) . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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_)\} / ^ \_ (/_/^\_\) . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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_)\} / ^ \_ (/_/^\_\) . . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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_)\} / ^ \_ (/_/^\_\) . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to input pictures automatically based on cell input? | Excel Worksheet Functions | |||
Return a value from a table using Column and row names as input? | Excel Discussion (Misc queries) | |||
store inventory sheet(ex:sports equipment store) | Excel Worksheet Functions | |||
CODE to select range based on User Input or Value of Input Field | Excel Programming | |||
Using Outlook with Excel to Input Names into Email | Excel Programming |