Combo box shows no content

kanadaaa

Active Member
Joined
Dec 29, 2019
Messages
291
Office Version
  1. 365
Platform
  1. Windows
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:
VBA 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:
VBA 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

Active Member
Joined
Dec 29, 2019
Messages
291
Office Version
  1. 365
Platform
  1. Windows
Both ThisWorkbook and Module1 are empty.
I have no idea 😇 Maybe it's just Office 365.
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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 :unsure:

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


Let me know. Thanks
 

kanadaaa

Active Member
Joined
Dec 29, 2019
Messages
291
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Sorry I meant to say workbook
- easy mistake :)

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

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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,163
Messages
5,623,103
Members
415,954
Latest member
TylerSeeds

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