0

I used a simple code to populate combo box from a list of worksheets dynamically. On Sat, the combo box was successfully populated.

On Sunday I played with the code by adding new form controls/new codes. Then suddenly the combo box population just stopped to populate values and there was no error message at all.

So I closed that workbook, rebooted the laptop, and started a new workbook with the very simple code that I got successful result on Sat. Still, it's not working and it doesn't give me any error message.

Then, I tried the code on another laptop with a new excel workbook. On another laptop, the combo box is successfully populated with the values I want.

What's going on with my 1st laptop? What can I do to make the combo box population work again?

The code is written in worksheet/Activate environment. Here is the code: 

Option Explicit

Private Sub Worksheet_Activate()

    Dim sh6 As Worksheet

    For Each sh6 In ThisWorkbook.Worksheets
        Me.Test2_cbbox.AddItem sh6.name
    Next sh6

End Sub
Tom Brunberg
  • 20,312
  • 8
  • 37
  • 54
Pj Yang
  • 1
  • 2

2 Answers2

1
Me.Test2_cbbox.AddItem sh6.name

is the syntax for an ActiveX control

while if your combobox is a Form control, then use:

Me.Shapes("Test2_cbbox").ControlFormat.AddItem sh6.Name
DisplayName
  • 13,283
  • 2
  • 11
  • 19
  • Thank you very much. But mine is a ActiveX control. By switching to "Me.Shapes("Test2_cbbox").ControlFormat.AddItem sh6.Name", I got run time error '438' saying that Object doesn't support this property or method. So I am still going to stick with the original code. But again, thank you for replying! – Pj Yang Oct 07 '19 at 15:40
  • you are welcome. if your combobox is an ActiveX one and your code doesn't work with the proper syntax, then go and check as per @SNicolaou comment – DisplayName Oct 07 '19 at 17:31
0

The link here identified my issue and suggest a few solutions. The #13 post at edited Dec 16 '14 at 8:22 is the one that solve my problem. Thank you all for helping.

Microsoft Excel ActiveX Controls Disabled?

Pj Yang
  • 1
  • 2
  • Well, the solution I posted on 10/7/19 worked for a week...Today my ActiveX controls again cannot function.....SOS!! – Pj Yang Oct 14 '19 at 03:44