Why Form Combobox VBA do not fill the combobox? Please help!

Hi all,

I am not sure where my script has gone wrong.

See attached excel file.

I am trying to have a few comboboxes which will return the quantity depending on the DATE, BARS.

I have created the date without issue. However the Bars did not work because when I select the from the drop down list, the box appear blank.

As well - how to write a script that if a particular bar is selected, what Items did they order using vertical data to appear horizontally and has to be Unique list too?

Thanks in advance.

To add items in combobox, do the following:

Double click the userform to go into the VBA window of the userform. There are 2 dropdown boxes at the top of the window, one is named "userform" and one is named "click". Change the click to "Initialize". Once you do it, this code will show in the window:

Private Sub UserForm_Initialize()


End Sub

Here is where you will add the items:

Private Sub UserForm_Initialize()
with combobox1
.additem "Item 1"
.additem "Item 2"
.additem "Item 3"
end with


End Sub

Thanks kjy1989 but it did not work nor I can find the UserForm_Initialize()

I am not using UserForm. I created comboboxes using Form and assigned macro to it with VB script.

Code as below

Sheet2(Sheet2)

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("$A:$A")) Is Nothing Then
     Call FilterUniqueDate
    End If

End Sub

Private Sub Worksheet_ChangeBARS(ByVal Target As Range)

    If Not Intersect(Target, Range("$B:$B")) Is Nothing Then
     Call Bars_ID
    End If

End Sub

Module 2

Sub FilterUniqueDate()
    Dim Lrow As Long, test As New Collection
    Dim Value As Variant, temp() As Variant
    ReDim temp(0)
    On Error Resume Next
    With Worksheets("Sheet2")
        Lrow = .Range("A" & Rows.Count).End(xlUp).Row
    temp = .Range("A2:A" & Lrow).Value
    End With
    For Each Value In temp
        If Len(Value) > 0 Then test.Add Value, CStr(Value)
    Next Value
    Worksheets("Sheet2").Shapes("UniqueDate").ControlFormat.RemoveAllItems
    For Each Value In test
         Worksheets("Sheet2").Shapes("UniqueDate").ControlFormat.AddItem Value
    Next Value
    Set test = Nothing
End Sub
Sub SelectedValueDate()
    With Worksheets("Sheet2").Shapes("UniqueDate").ControlFormat
        Worksheets("Sheet2").Range("J6") = .Value
        
    End With
End Sub

Module 3

Sub Bars_ID()
    Dim Lrow As Long, test As New Collection
    Dim Value As Variant, temp() As Variant
    ReDim temp(0)
    On Error Resume Next
    With Worksheets("Sheet2")
        Lrow = .Range("B" & Rows.Count).End(xlUp).Row
    temp = .Range("B2:B" & Lrow).Value
    End With
    For Each Value In temp
        If Len(Value) > 0 Then test.Add Value, CStr(Value)
    Next Value
    Worksheets("Sheet2").Shapes("BarsID").ControlFormat.RemoveAllItems
    For Each Value In test
         Worksheets("Sheet2").Shapes("BarsID").ControlFormat.AddItem Value
    Next Value
    Set test = Nothing
End Sub

Sub SelectedValueBars()
    With Worksheets("Sheet2").Shapes("BarsID").ControlFormat
        Worksheets("Sheet2").Range("J8") = .List(.Value)
    End With
End Sub