Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform cannot return any values
Hiya yet again
This time I would like a userform to be able to update a public array defined in a normal module. I wrote code in the class module for my form to perform the update when it closes but it returns an error, thinking the array is a missing sub. I don't really want to have to write to a worksheet then retrieve the values from there. Is there any way I can get a userform to return a value depending on options chosen? Thanks in advance Nick |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform cannot return any values
What is the actual error?
"Nick" wrote: Hiya yet again This time I would like a userform to be able to update a public array defined in a normal module. I wrote code in the class module for my form to perform the update when it closes but it returns an error, thinking the array is a missing sub. I don't really want to have to write to a worksheet then retrieve the values from there. Is there any way I can get a userform to return a value depending on options chosen? Thanks in advance Nick |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform cannot return any values
Since the array is global in a general module, the userform can update the
array. It doesn't return it. General Module Dim varr() as Variant Sub showForm() Userform1.Show for i = lbound(varr) to ubound(varr) debug.print varr(i) Next end Sub in the userform module Private Sub CommandButton1_Click() unload me redim varr(1 to 10) for i = 1 to 10 varr(i) = i^3 Next End Sub -- Regards, Tom Ogilvy "Nick" wrote in message ... Hiya yet again This time I would like a userform to be able to update a public array defined in a normal module. I wrote code in the class module for my form to perform the update when it closes but it returns an error, thinking the array is a missing sub. I don't really want to have to write to a worksheet then retrieve the values from there. Is there any way I can get a userform to return a value depending on options chosen? Thanks in advance Nick |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform cannot return any values
I'll explain myself better.
I have a form with some checkboxes, say 10 of them. When the user have selected the options he wants he would then click the Ok button. I want a procedue behind the Ok button to hide the form then assign the values of the checkboxes to an array which then can be used by a different module to perform some tasks. But. You can't publicly declare an array in a class module (the module behind the form). Therefore i cannot assign the values to an array which can be read by any of my normal modules. If I try to do this then the module doesn't recognise the existance of the array and thinks it is trying to run a sub, then politely informs me the sub doesn't exist. The only work around I can see to this is to write the values to a worksheet then write more code in another module to retrieve these values. It is a bit ungainly though and should be unneccessary. Hope this makes it a bit clearer. Nick |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform cannot return any values
Thanks Tom - I thought of that but it didn't work.
Double checking I found that I had only 'dimmed' the array outside of subs in the general module rather than using the public keyword. I feel rather stupid now : ) Thanks anyhow Nick |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform cannot return any values
See my post for a method that works.
-- Regards, Tom Ogilvy "Nick" wrote in message ... I'll explain myself better. I have a form with some checkboxes, say 10 of them. When the user have selected the options he wants he would then click the Ok button. I want a procedue behind the Ok button to hide the form then assign the values of the checkboxes to an array which then can be used by a different module to perform some tasks. But. You can't publicly declare an array in a class module (the module behind the form). Therefore i cannot assign the values to an array which can be read by any of my normal modules. If I try to do this then the module doesn't recognise the existance of the array and thinks it is trying to run a sub, then politely informs me the sub doesn't exist. The only work around I can see to this is to write the values to a worksheet then write more code in another module to retrieve these values. It is a bit ungainly though and should be unneccessary. Hope this makes it a bit clearer. Nick |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform cannot return any values
Note my example was incorrect as well
Dim varr() as Variant should have been Public Varr() as Variant -- Regards, Tom Ogilvy "Nick" wrote in message ... Thanks Tom - I thought of that but it didn't work. Double checking I found that I had only 'dimmed' the array outside of subs in the general module rather than using the public keyword. I feel rather stupid now : ) Thanks anyhow Nick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Exclude #N/A values and Return Numeric values to consecutive cells in Single Row | Excel Worksheet Functions | |||
Userform Return Key Behaviour | Excel Discussion (Misc queries) | |||
Search multiple values to return single values | Excel Worksheet Functions | |||
Keep userform visible, but return control to calling routine | Excel Programming | |||
VBA Syntax for VLOOKUP to return array of return values | Excel Programming |