Array pulls in incorrect data from worksheet

[ipbr21054]

I have the following code in a module.

 Option Base 1

Function ControlNames() As Variant
ControlNames = Array("txtCustomer", "txtRegistrationNumber", "txtBlankUsed", "txtVehicle", _
                    "txtButtons", "txtKeySupplied", "txtTransponderChip", "txtJobAction", _
                    "txtProgrammerCloner", "txtKeyCode", "txtBiting", "txtChassisNumber", _
                    "txtJobDate", "txtVehicleYear", "txtPaid", "txtInvoiceNumber", "TextBox1", _
                    "TextBox2", "TextBox3", "TextBox4", "TextBox5", "TextBox6", "TextBox7", "TextBox9", "txtPinCode")
End Function

Function IsComplete(ByVal Form As Object) As Boolean
    Dim i As Integer
    For i = 1 To UBound(ControlNames)
        IsComplete = CBool(Len(Form.Controls(ControlNames(i)).Text) > 0)
        If Not IsComplete Then
            MsgBox "PLEASE COMPLETE ALL FIELDS", 17, "Entry Required"
            Form.Controls(ControlNames(i)).BackColor = vbMagenta
            Form.Controls(ControlNames(i)).SetFocus
            Exit Function
        End If
    Next i
End Function

All the above entries from my worksheet to userform are correct APART from TextBox9
In TextBox9 the value has been taken from worksheet column X which is incorrect as it should be taking the value from column AB

My issue is looking at all the code for hours i just cant see or find what tells the userform fields where to get data from to populate them.
Ive looked for at least 2 hours this morning.

Does the code above advise you of anything thanks.
Something is happening as TextBox9 is being populate but trying to locate what is telling it to look at column what column

 

[johnnyL]

"TextBox9" is the 24th entry in ControlNames ... the 24th letter of the alphabet is X

 

[ipbr21054]

Ok I now see that but what or how do I achieve the Textbox9 pulling from AB

Thanks.

 

[johnnyL]

One way would be to check the value of 'i' and if it is 24 then change it to 28 ... then before the 'Next i' line of code,Check if 'i' is 28 and if it is set the value of 'i' back to 24.

 

[johnnyL]

That is not the best way to do it, but in this case it should work.

 

[ipbr21054]

I will check tomorrow.
I don’t think it will be 24 as today I added an the extra Textbox.
So before I did that for what reason would 24 be there

 

[Norie]

You could add a separate array for the column numbers.

Or create a dictionary where the keys are the control names and the values are the columns.

 

[ipbr21054]

Thanks but unable to locate the code so i will go to plan B

Thanks