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

how exclude item in combobox based on word in another column

0

hello ,

I  search for addition condition  when populate  items  in combobox. so  what  I  want  should  just  show  the  items  in combobox  based  on  column D  if  word CUST  is    existed in  adjacent cell for  column C   then  should  not  the  populate items  in combo box  after  matching  column D  if  the  adjacent cells in  column C  is  not  equal CUST  . in  this  case  should  not  populate  item  CL5 in combobox  because  the  the  adjacent cells in  column C  is equal supp   (should  be  CUST

Private Sub UserForm_Initialize()
Dim i As Long
For i = 2 To 1000
If Range("c2:c" & i) <> "CUST" Then
If WorksheetFunction.CountIf(Range("D2:D" & i), Range("D" & i)) = 1 Then
Me.Combobox1.AddItem Range("D" & i)
End If
End If
Next i
End Sub
Answer
Discuss

Answers

0
Selected Answer

Leap

I think I understand your question and feel you have two mistakes in your code- you were NOT checking a single cell was equal to "cust" (before checking if the item had been added to the ComboBox list) and need to use COUNTIFS to add a second criteriia to the count (i.e. that C&i is "cust" as well as D&i). I've declared a new variable Only so you need to change it in one place only.  I've made the changes in bold below (and in the attached file):

Private Sub UserForm_Initialize()
Dim i As Long
Dim Only As String
' say what C should be for an item to be added to the ComboBox
Only = "cust"

For i = 2 To 10
    If Range("c" & i) = Only Then
        If WorksheetFunction.CountIfs(Range("D2:D" & i), Range("D" & i), Range("C2:C" & i), Only) = 1 Then
            Me.Combobox1.AddItem Range("D" & i)
        End If
    End If
Next i

End Sub

Hope this fixes it.

Discuss

Discussion

thanks  again  for  your  help !
leap (rep: 34) Jun 19, '22 at 8:23 am
Add to Discussion


Answer the Question

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