Combo box shows no content

[kanadaaa]

Hi, I have a question about the combo box.
When I used the data validation function to add a dropdown list into a cell, the options were too small and I looked for a way to make them bigger.
It seems there are two ways to do this: (i) add a macro to increase the zoom percentage at which you view the sheet, or (ii) use the combo box.
About (i), I asked in a different thread and reached the following code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim lZoom As Long
  Dim lZoomDV As Long
  Dim lDVType As Long
  lZoom = 64
  lZoomDV = 120
  lDVType = 0
  Application.EnableEvents = False
  On Error Resume Next
  lDVType = Target.Validation.Type
    On Error GoTo errHandler
    If lDVType <> 3 Then
      With ActiveWindow
        If .Zoom <> lZoom Then
          .Zoom = lZoom
          .ScrollColumn = 1
          .ScrollRow = 1
        End If
      End With
    Else
      With ActiveWindow
        If .Zoom <> lZoomDV Then
          .Zoom = lZoomDV
        End If
      End With
    End If
exitHandler:
  Application.EnableEvents = True
  Exit Sub
errHandler:
  GoTo exitHandler
End Sub

This works well but if possible, I'd prefer (ii) because the font itself seems to be able to be enlarged.
Then, I followed the instruction provided at Excel Data Validation Combo box and am using the following code:

'==========================
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
  Cancel As Boolean)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Dim Tgt As Range
Set Tgt = Target.Cells(1, 1)
Set ws = ActiveSheet
On Error GoTo errHandler

If Tgt.Validation.Type = 3 Then
    Cancel = True
End If

Set cboTemp = ws.OLEObjects("TempCombo")
  On Error Resume Next
  With cboTemp
    .ListFillRange = ""
    .LinkedCell = ""
    .Visible = False
  End With
On Error GoTo errHandler

If Tgt.Validation.Type = 3 Then
    Application.EnableEvents = False
    str = Tgt.Validation.Formula1
    str = Right(str, Len(str) - 1)
    With cboTemp
      .Visible = True
      .Left = Tgt.Left
      .Top = Tgt.Top
      .Width = Target.Width + 15
      .Height = Target.Height + 5
      .ListFillRange = ws.Range(str).Address
      .LinkedCell = Tgt.Address
    End With
    cboTemp.Activate
    'open the drop down list automatically
    Me.TempCombo.DropDown
  End If
  
errHandler:
  Application.EnableEvents = True
  Exit Sub

End Sub
'=========================================
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet

Set cboTemp = ws.OLEObjects("TempCombo")
  On Error Resume Next
If cboTemp.Visible = True Then
  With cboTemp
    .Top = 10
    .Left = 10
    .ListFillRange = ""
    .LinkedCell = ""
    .Visible = False
    .Value = ""
  End With
End If

errHandler:
  Application.EnableEvents = True
  Exit Sub

End Sub
'====================================
'Optional code to move to next cell if Tab or Enter are pressed
'from code by Ted Lanham
'***NOTE: if KeyDown causes problems, change to KeyUp
'Table with numbers for other keys such as Right Arrow (39)
'https://msdn.microsoft.com/en-us/library/aa243025%28v=vs.60%29.aspx

Private Sub TempCombo_KeyDown(ByVal _
        KeyCode As MSForms.ReturnInteger, _
        ByVal Shift As Integer)
    Select Case KeyCode
        Case 9 'Tab
            ActiveCell.Offset(0, 1).Activate
        Case 13 'Enter
            ActiveCell.Offset(1, 0).Activate
        Case Else
            'do nothing
    End Select
End Sub
'====================================

But when I double-click on a cell with a data validation setting of List (in which the source is set for something like "=TableName"), the combo box appears but there's no selectable content in it.
I'm wondering how I can resolve this problem. Please give me a hand. Thank you.

 

[kanadaaa]

Both ThisWorkbook and Module1 are empty.
I have no idea ? Maybe it's just Office 365.

 

[Yongle]

Maybe it's just Office 365.

Really ?
- I also use 365
If you are correct, why does it work for me ?
- I think there is another reason, but we do not yet know what it is

Perhaps the problem is in your workbook
- create a NEW workbook and test again


Let me know. Thanks

 

[kanadaaa]

I gave it a try again on a brand new worksheet but the behaviour didn't change.
It's so strange it works for you but it doesn't for me.
There might be some excel-external reasons, but I have absolutely no idea what they are.

 

[Yongle]

a brand new worksheet

I suggested trying it in a new workbook

 

[kanadaaa]

I suggested trying it in a new workbook

Sorry I meant to say workbook

 

[Yongle]

Sorry I meant to say workbook

- easy mistake

Active-x objects have historically caused unexplainable problems like this for some users

You could eliminate the issue by creating a simple userform to do what you want
- the userform could be manipulated in the same way as the active-x combobox