Excel Form - For/Next - Naming Convention Problem

[mikefromchitown]

Hi,

I'm hoping that someone can help me out. The Range statement below has an issue with the right hand side of the equal sign. If I leave out the "& i", the information in the first text box is copied into all of the cells. However, I want to copy info from multiple text boxes. Does anyone see what I'm doing wrong?

Thanks,
Mike

Private Sub Save_Click()
'Transfers all values from Userform to Excel sheet
For i = 1 To 10
Range("PWTitle" & i).Value = ProgramWork.PWTitle & i.Text
Next i
End Sub

[Comatose]

It might be a bit more helpful if you can attach the excel document (as a .zip if necessary). One problem, is that you are trying to reference a property of an integer, which is not an object. For example, the variable i is going to be a sequence of numbers starting at 1, and ending at 10. So, the first time through the loop, I will be 1 then 2, etc.

So, even though you are trying to concatenate ProgramWork.PWTitle and the number referenced by i. VB still thinks you are trying to get the .text property for the number represented by i. So, it flips out. You MIGHT be able to make it work like this Range("PWTitle" & i).Value = ProgramWork.PWTitle & i & .Text but I'm not sure that will fly either. Let me know how it turns out, or attach the excel document.

[mikefromchitown]

It didn't work for me, but I've attached the code if you would like to take a look.

Thanks,
Mike

[Comatose]

Sorry it has taken so long to respond, I've had a lot on my plate....

The problem here, is that what you want to do, requires a control array. VBA (Excel) doesn't allow control arrays, and therefore means you have to modify your for loop.

An array, is a variable that has many slots for values. I try to describe the simplest form of an array, like an egg carton, with 1 row for eggs. There is only 1 egg carton (1 variable, say X) but a number of places to put eggs (called an index). So, The variable X can be like this:

X(0) = "hello"
X(1) = "goodbye"
X(2) = "some more stuff"

Well, you can do the same thing with controls (at least, in VB4, 5 and 6). You can make a control array (which would be many controls, all with the same name), in two ways. 1, is to put a control on the form, with the same name as another control (lets say, you add two command buttons, command1 and command2, then change command2's name to command1) and VB will notice there is already a command1, and ask you if you want to make a control array. The other way, is when you add a control to the form, change it's "index" property to 0. Then, whenever you name a control the same name, it will just change the index of that control.

The bad news, is VBA (the VB that comes packaged with office [ie excel]) does NOT support this. So, instead of using a traditional "for loop", we are going to use a modified version, called "for each". That basically does the same thing as a for loop, but it will automagically calculate how many times it needs to loop (ya know, for each item involved). If you change your code to this:

For Each ctrl In DataForm.Controls
    MsgBox ctrl.Text
Next ctrl

it should pop up a messagebox with all the values..... now just replace the msgbox with your range code, and it should work alright.