ComboBox not displaying added values

always_confused

Board Regular
Joined
Feb 19, 2021
Messages
66
Office Version
  1. 2016
Platform
  1. Windows
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:

VBA Code:
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:

VBA Code:
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?
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,161
Office Version
  1. 2016
Platform
  1. Windows
Don't understand your code. You created ComboBox in MySheet, get list from lot then fill into ComboBox in DerivePosage. :unsure:

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

Board Regular
Joined
Feb 19, 2021
Messages
66
Office Version
  1. 2016
Platform
  1. Windows
Don't understand your code. You created ComboBox in MySheet, get list from lot then fill into ComboBox in DerivePosage. :unsure:

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

Well-known Member
Joined
Nov 26, 2020
Messages
1,161
Office Version
  1. 2016
Platform
  1. Windows
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
VBA Code:
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
 
Solution

always_confused

Board Regular
Joined
Feb 19, 2021
Messages
66
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
VBA Code:
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")?
 

always_confused

Board Regular
Joined
Feb 19, 2021
Messages
66
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Nov 26, 2020
Messages
1,161
Office Version
  1. 2016
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,680
Messages
5,637,749
Members
416,982
Latest member
lisam77

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top