ComboBox not displaying added values

[always_confused]

Hello,

I have a sheet with time stamp values in column B. They are formatted as DD/MM/YYYY HH:MM:SS. I am trying to make a combobox to use as a menu to select a MM/YYYY for further calculations. My issue is:

I have 2 subs, one to make the combobox:

Sub make_box()
Dim pos As Range
Set pos = Sheets("MySheet").Range("V2:W4")

ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
    DisplayAsIcon:=False, Left:=pos.Left, Top:=pos.Top, Width:=pos.Width, Height:=pos.Top) _
    .Select
End Sub

and one to fill the box:

Sub Populate_combobox_with_Unique_values()

    Dim vStr, eStr
    Dim dObj As Object
    Dim xRg As Range
    
    Dim rng As Range
    Dim lastrow As Long
    lastrow = Sheets("lot").Range("B" & Rows.Count).End(xlUp).Row
    Set rng = Sheets("lot").Range("B2:B" & lastrow)
    
    
    On Error Resume Next
    Set dObj = CreateObject("Scripting.Dictionary")
    Set xRg = rng
    vStr = xRg.Value

    With dObj
        .comparemode = 1
        For Each eStr In vStr
            If Not .exists(Mid(eStr, 4, 7)) And eStr <> "" Then .Add Mid(eStr, 4, 7), Nothing
        Next
        If .Count Then
            Sheets("DerivePosage").ComboBox1.List = WorksheetFunction.Transpose(.keys)
        End If
    End With

End Sub

I can run the first Sub and then the second one, and it works fine. However, when I try to call them together either in the same Sub by just copying and pasting their content into the same Sub, or if I call them both in a Sub, the box is made, but it's empty.
A colleague tried the copy and paste method on his computer (Excel 2019) and it worked. On my computer (Excel 2016) it does not. Is this possibly a limit of my Excel or am I doing something wrong?

 

[Zot]

Don't understand your code. You created ComboBox in MySheet, get list from lot then fill into ComboBox in DerivePosage.

Anyway, I'm not sure if you can fill ComboBox list like that from Dictionary. Did not work in my Excel 2016 either.

 

[always_confused]

Don't understand your code. You created ComboBox in MySheet, get list from lot then fill into ComboBox in DerivePosage.

Anyway, I'm not sure if you can fill ComboBox list like that from Dictionary. Did not work in my Excel 2016 either.

Oh sorry yeah, I messed up replacing my sheet names with generic stuff. Can't edit the post for some reason though. ComboBox created in MySheet and filled in MySheet from Dictionary fillet with data from lot

 

[Zot]

Oh sorry yeah, I messed up replacing my sheet names with generic stuff. Can't edit the post for some reason though. ComboBox created in MySheet and filled in MySheet from Dictionary fillet with data from lot

I'm experiencing the same thing like yours. If I run sub to create ComboBox and then rub sub to fill the list like in the code, it worked. I created a new sub to call the two subs in sequence but it did not work also. I need to re-arrange the code so that I get the list first and then created the ComboBox to fill in. Then it is okay. However, using the like below still cannot work even if it worked as individual sub:
ActiveSheet.ComboBox1.List = WorksheetFunction.Transpose(.keys)

Here is the code that works

Sub make_box()

    Dim vStr, eStr, key
    Dim dObj As Object
    Dim xRg As Range
    Dim rng As Range
    Dim lastrow As Long
    
    lastrow = Sheets("lot").Range("B" & Rows.Count).End(xlUp).Row
    Set rng = Sheets("lot").Range("B2:B" & lastrow)
    
    Sheets("MySheet").Range("A1").Select
    On Error Resume Next
    Set dObj = CreateObject("Scripting.Dictionary")
    vStr = rng.Value
    Application.ScreenUpdating = False
    With dObj
        .comparemode = 1
        For Each eStr In vStr
            If Not .Exists(eStr) And eStr <> "" Then .Add eStr, Nothing
        Next
        If .Count Then

        End If
    End With
    
    Dim pos As Range

    Set pos = Sheets("MySheet").Range("V2:W4")
    
    With Sheets("MySheet").OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
        DisplayAsIcon:=False, Left:=pos.Left, Top:=pos.Top, Width:=pos.Width, Height:=pos.Top)
        With .Object
'            .List = WorksheetFunction.Transpose(.keys)                    ' This cannot work somehow
            For Each key In dObj
                .AddItem key
            Next
        End With
    End With
    Application.ScreenUpdating = True
    
End Sub

 

[always_confused]

I'm experiencing the same thing like yours. If I run sub to create ComboBox and then rub sub to fill the list like in the code, it worked. I created a new sub to call the two subs in sequence but it did not work also. I need to re-arrange the code so that I get the list first and then created the ComboBox to fill in. Then it is okay. However, using the like below still cannot work even if it worked as individual sub:
ActiveSheet.ComboBox1.List = WorksheetFunction.Transpose(.keys)

Here is the code that works

Sub make_box()

    Dim vStr, eStr, key
    Dim dObj As Object
    Dim xRg As Range
    Dim rng As Range
    Dim lastrow As Long
   
    lastrow = Sheets("lot").Range("B" & Rows.Count).End(xlUp).Row
    Set rng = Sheets("lot").Range("B2:B" & lastrow)
   
    Sheets("MySheet").Range("A1").Select
    On Error Resume Next
    Set dObj = CreateObject("Scripting.Dictionary")
    vStr = rng.Value
    Application.ScreenUpdating = False
    With dObj
        .comparemode = 1
        For Each eStr In vStr
            If Not .Exists(eStr) And eStr <> "" Then .Add eStr, Nothing
        Next
        If .Count Then

        End If
    End With
   
    Dim pos As Range

    Set pos = Sheets("MySheet").Range("V2:W4")
   
    With Sheets("MySheet").OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
        DisplayAsIcon:=False, Left:=pos.Left, Top:=pos.Top, Width:=pos.Width, Height:=pos.Top)
        With .Object
'            .List = WorksheetFunction.Transpose(.keys)                    ' This cannot work somehow
            For Each key In dObj
                .AddItem key
            Next
        End With
    End With
    Application.ScreenUpdating = True
   
End Sub

Why do you select Sheets("MySheet").Range("A1")?

 

[Zot]

Why do you select Sheets("MySheet").Range("A1")?

? I did not notice it was there. I was playing around with the code. I think the code can be optimized more perhaps.

Yes.... no need to select that

 

[always_confused]

Ok so now I'm in the dumb situation of having a combox box and not knowing how to use the selected value. I would like to use the data selected in the combobox in another sub. Do you know how to access the value selected? I can't figure out the correct syntax for something like "CoboBox1.value"

 

[Zot]

Ok so now I'm in the dumb situation of having a combox box and not knowing how to use the selected value. I would like to use the data selected in the combobox in another sub. Do you know how to access the value selected? I can't figure out the correct syntax for something like "CoboBox1.value"

Can refer here

Get the selected value of a activex combobox using vba

How do i get the selected value of the combobox? i have a combobox which has the values: "Corporate" and "Consumer". I want to get the value that i selected, not the index, and store in a string.

stackoverflow.com

 

[always_confused]

Can refer here

Get the selected value of a activex combobox using vba

How do i get the selected value of the combobox? i have a combobox which has the values: "Corporate" and "Consumer". I want to get the value that i selected, not the index, and store in a string.

stackoverflow.com

Thank you