Hi guys, strugled with the name a little bit, but I think it is pretty accurate.
This is more a "for interest" question as I have thought of another way to get around the problem, but I still wouldn't mind knowing if it is possible.
I started out with a program that allows me to enter in the parameters for up to ten different variations of a design. Originally these were entered on one userform within different multipages for each variation. Once all the values were entered for each multipage, the values in the controls were entered into the spreadsheet, calculated, then returned to the userform in a results section. The code for the values being entered into the spreadhseet and the updating of the results was all in a public module, called by an OnClick command. The data is organised in columns in the spreadsheet (one design per column), with the names of the cells in each row being the same, but with the number from 1 to 10 at the end of the name (ie. result1, result2...).
This mean that the updating for each design was done within one pretty simple module similar to;
However, there were too many controls and I was getting an out of memory error when running the userform. Hence, I split up the userforms so there was one per design. This became a problem though, when I realised the public module would have to be repeated for each userform, as the above code shows the userform2 code would be;
Range("data" & i).Value = Userform1.Controls.item("data" & i).Value
I have tried some code at the start to try and make the name of the userform variable, but none of the following seemed to work for me;
Range("data" & i).Value = Userform2.Controls.item("data" & i).Value
After that and a few other silly ideas, my limited understanding ran out. Is it possible to have a variable reference to a userform name? Or am I dreaming?
Dim usef as userform
Set usef = userforms("userform" & i)
dim usef as userform
dim namef as string
namef = "userform" & i
Set usef = userforms(namef)
Otherwise, the way I will get around it is to call a single userform, set the design number when entering the userform, then use this to change the spreadsheet references, but keep the userform reference the same;
Sorry this is such a long post, but I felt it necessary to explain the whole situation. Looked everywhere for an answer to this, including some VBA for excel programming manuals & still couldn't find anything.
Range("data" & i).Value = userform1.controls.data