Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

popluate items doesn't give correct location based on sheet

0

Hi

this  is  my  project  was  work, now  it  doesn't work  after  change  comboboxes  location .so  when  select  the  combobox2 will fill automatically comboboxes13,24,35  this  is  what  I  want  but  if  I  select   combobox3  doesn't  fill items  into comboboxes 14,24,36  correctly .

every  combobox is  existed  under  lable CODE   should  link with column B and   combobox is  existed  under  lable BRAND should  link with column C ,combobox is  existed  under  lable TYPE should  link with column D and  combobox is  existed  under  lable ORIGIN should  link with column E. so every combobox is  existed under  lable CODE  when  select item should  fill automatically for adjacent othere comboboxes .I want  sharing  part  of  my  project  to  see  the  problem as  in attached file .

I  hope  somebody  guide  me  where  is  my  mistake and fix  it ,please

excel
MS Office Version: 2019
posted by: Mussa (rep: 48) on Wed Aug 24, 2022 at 12:11 pm
Answer
Discuss

Answers

0
Selected Answer

Mussa

The problem is your UF code:

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

        Next
    End With
End With


End Sub

That worked when your ComboBoxes (CBs) were called CB2, CB6, CB10 etc. but you renumbered them to  CB2, CB3, CB4 etc. That means you can simplify your code (with an extra loop) to this (changes in bold):

Private Sub UserForm_Initialize()

With Sheets("BRANDS")
   With .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))
    ' loop ComboBoxes
    For CmB = 2 To 12
        ' loop data
        For i = 2 To 5
            Me.Controls("ComboBox" & CmB).List = .Offset(, 1).Value
        Next i
    Next CmB
    End With
End With

End Sub

Hope this fixes things for you.

posted by: John_Ru (rep: 6142) on Wed Aug 24, 2022 at 2:46 pm
Discuss

Discussion

thanks John !
this  works  greatly .
Mussa (rep: 48) Aug 24, '22 at 4:56 pm
Add to Discussion


Answer the Question

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