how fix error when run UserForm_Initialize?

0

hello

I'm  so  confused  about  this  error  in this  line

Me.Controls("ComboBox" & i).List = .Offset(, i - 1).Value

"could  not  set  the list property.invalid  property array index"  .the  problem  doesn't  always  occures   when  run  the  useform.

in  normal  case  works  well  when  I  run  the  userform, suddenly  today when  run  userform  it  shows  the  error . how  can  I fix  it   forever ,please 

this  the  code   occures problem

Private Sub UserForm_Initialize()
With Sheets("DATA")
   With .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))
        For i = 1 To 4
            Me.Controls("ComboBox" & i).List = .Offset(, i - 1).Value
            Me.Controls("ComboBox" & i + 4).List = .Offset(, i - 1).Value
            Me.Controls("ComboBox" & i + 8).List = .Offset(, i - 1).Value
            Me.Controls("ComboBox" & i + 12).List = .Offset(, i - 1).Value
            Me.Controls("ComboBox" & i + 16).List = .Offset(, i - 1).Value
            Me.Controls("ComboBox" & i + 20).List = .Offset(, i - 1).Value
            Me.Controls("ComboBox" & i + 24).List = .Offset(, i - 1).Value
            Me.Controls("ComboBox" & i + 28).List = .Offset(, i - 1).Value
            Me.Controls("ComboBox" & i + 32).List = .Offset(, i - 1).Value
            Me.Controls("ComboBox" & i + 36).List = .Offset(, i - 1).Value
            Me.Controls("ComboBox" & i + 40).List = .Offset(, i - 1).Value

             Me.Controls("ComboBox" & i + 44).List = .Offset(, i - 1).Value
        Next
    End With
End With

I  hope  to find  solution  for  this  dilemma

Answer
Discuss

Discussion

Mussa

I'm not clear what data (offset ranges seemingly) you're using with the code to start the UserForm.

Please add a representative Excel file to your original question (plus some words of clarification).
John_Ru (rep: 2147) Oct 9, '21 at 7:04 am
see  my  file  ,please .
actually  I  no  know  what  I can  say  .  but   now  when  run  the  userform  it  will show  error  as  I  said  . it  seems  problem  from  multiple  comboboxes  which  are  existed in direction of right   the  userform,   but  sometimes  work s without  error  .I  don't  have  any  explenation  why  happens this  .   I  hope  to  find  the  reason based on my  file
Mussa (rep: 16) Oct 9, '21 at 8:50 am
Add to Discussion

Answers

0
Selected Answer

Mussa

I think the error is that your loop does not match some ComboBox names in your UserForm1...

In the orange portion, you have a single ComboBox1 but the yellow portion has controls called ComboBox2, 6, 10  etc. and ComboBox3, 7, 11 etc. and so on but to ComboBox45

One problem is that that you final line:

'Me.Controls("ComboBox" & i + 44).List = .Offset(, i - 1).Value
(now commented out) can produce ComboBox names beyond ComboBox45 (which don't exist - perhaps you deleted them).

Another problem is that after the row with ComboBox 22, the ComboBoxes were confused between columns wnet 29... rather than 26...). 

Finally your loop  For i=1 to 4...ought to read  2 to 5 (so beginning with ComboBox 2 and ending with 5+40= ComboBox45) as below, changes in bold:

Private Sub UserForm_Initialize()

With Sheets("BRANDS")
   With .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))
        For i = 2 To 5
            Me.Controls("ComboBox" & i).List = .Offset(, i - 1).Value
            Me.Controls("ComboBox" & i + 4).List = .Offset(, i - 1).Value
            Me.Controls("ComboBox" & i + 8).List = .Offset(, i - 1).Value
            Me.Controls("ComboBox" & i + 12).List = .Offset(, i - 1).Value
            Me.Controls("ComboBox" & i + 16).List = .Offset(, i - 1).Value
            Me.Controls("ComboBox" & i + 20).List = .Offset(, i - 1).Value
            Me.Controls("ComboBox" & i + 24).List = .Offset(, i - 1).Value
            Me.Controls("ComboBox" & i + 28).List = .Offset(, i - 1).Value
            Me.Controls("ComboBox" & i + 32).List = .Offset(, i - 1).Value
            Me.Controls("ComboBox" & i + 36).List = .Offset(, i - 1).Value
            Me.Controls("ComboBox" & i + 40).List = .Offset(, i - 1).Value
            'Me.Controls("ComboBox" & i + 44).List = .Offset(, i - 1).Value
        Next
    End With
End With

End Sub
The revised code and corrected UserForm are in the attached sheet

Hope this fixes things for you.

Discuss

Discussion

um ! so unarranged the combo boxes make causes the problems.
thanks  John  for  this  important point 
Mussa (rep: 16) Oct 9, '21 at 2:55 pm
In part, yes, Mussa but the real (code failure) problem was referring to ComboBoxes which didn't exist. Thanks for selecting my Answer, glad it helped.
John_Ru (rep: 2147) Oct 9, '21 at 3:56 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login