Solved: Loop through controls and add data from cells

[swsouth]

Hi all,

This is my first time here in 4 years. That was the last time I worked with VBA, so needless to say I'm knocking off a lot of rust. I should also mention I was just a novice 4 years ago and I've now risen to the rank of clueless!
Anyway, I'm trying to loop through textboxes on my multipage form and it keeps crashing with this error, "Run-time error 1004: Method 'Range' of object '_Global' failed. I'm at the point of pulling my hair out now! This is in the UserForm2_Initialize section and I've also tried it in a separate module. That would actually be my preference for location, as I need to run this action on several more pages and worksheets, so ultimately I've got to make it an accessible procedure to all and hopefully send the page and matching sheet in and populate textboxes with data from sheets. But that's a lofty goal at this point. I'll be happy if I can just get it working on the first page.

Here's my current code:

[vba]Public Sub prepTextBoxes_Community()
Dim C As Integer
Dim R As Integer
Dim ctrl As Control

Sheets("Community").Activate
Range("C2").Select

C = ActiveCell.Column
R = ActiveCell.Row

For Each ctrl In Me.MultiPage1.Pages(0).Controls
If TypeOf ctrl Is MSForms.TextBox Then
Range(C & R).Select
ctrl.Value = ActiveCell.Value
C = C + 1
End If
Next ctrl
End Sub[/vba]

If someone could please point me in the right direction on this, it would be very much appreciated. Thanks in advance for any help given.

SS

[mikerickson]

Try
[VBA]Public Sub prepTextBoxes_Community()
Dim aCell as Range
Dim ctrl As Control

Set aCell = ThisWorkbook.Sheets("Community").Range("C2")

For Each ctrl In Me.MultiPage1.Pages(0).Controls
If TypeName(ctrl) = "TextBox" Then Then
With aCell
ctrl.Value = CStr(.Value)
Set aCell = .Offset(0,1)
End With
End If
Next ctrl
End Sub [/VBA]

[swsouth]

WOW! Works like a champ!! Thank you, thank you, thank you so much mikerickson. I really appreciate the help. I'll look into those additions you made so I can understand what I was doing wrong. Thank you again for the extremely quick help.

SS

[mikerickson]

The reason for your error was the Range(C & R).

Both C and R are integers, so (for example) you are asking it to find Range("23"), which doesn't exists.

You could have used Cells(R, C), but by removing the unneeded (and slowing) selection from the routine, neither of those syntaxes were needed.