My Loop is Reversing. Any Idea Why?

afrazier1221

New Member
Joined
Jul 23, 2023
Messages
20
Office Version
  1. 365
Platform
  1. Windows
I have this:
Dim contr As Control
C = 1
For Each contr In UserForm1.Controls
If Left(contr.Name, 4) = "CDTB" Then
contr.Value = ""
contr.Value = Sheet5.Cells(C, 41).Value
C = C + 1
End If
Next
On the UserForm in question, there are eighteen textboxes, named CDTB1 through CDTB18 (Customer Data Text Box). The loop is meant to populate the form fields from Sheet5, Cells AO1 through AO18 (Rows 1 through 18, Column 41).

The code result reverses itself at 10 and produces essentially a mirrored result.

What I should get:

Good.jpg


What I actually get:

Bad.jpg


I'm not sure why it's doing this. Any thoughts?
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
The for each loop goes in order of control creation. Given that they are numbered, you should use that:

Code:
Dim contr As Control
For Each contr In UserForm1.Controls
If Left(contr.Name, 4) = "CDTB" Then
C = Clng(Mid$(contr.name, 5))
contr.Value =Sheet5.Cells(C, 41).Value
End If
Next
 
Upvote 0
You're assuming that the order of a control in the controls is numerical. Try this, considering that you are only interested in the CDTB controls.
VBA Code:
    Dim intCntr As Integer
    With Userform1
        For intCntr = 1 To 18
            .Controls("CDTB" & intCntr).Value = Sheet5.Cells(intCntr, 41).Value
        Next
    End With
 
Upvote 1
Solution
You're assuming that the order of a control in the controls is numerical. Try this, considering that you are only interested in the CDTB controls.
VBA Code:
    Dim intCntr As Integer
    With Userform1
        For intCntr = 1 To 18
            .Controls("CDTB" & intCntr).Value = Sheet5.Cells(intCntr, 41).Value
        Next
    End With
This worked, thank you.

I would still like to understand how to identify the problem, though. I went through the Properties list, but couldn't find anything to identify the controls' order of creation, or their numerical order, outside of the numeric sequence I gave them myself with the assigned names. Is there a place where I can see this information, or to perhaps alter it to put the controls in numeric sequence?
 
Upvote 0

Forum statistics

Threads
1,223,948
Messages
6,175,580
Members
452,653
Latest member
craigje92

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top