kanadaaa
Active Member
- Joined
- Dec 29, 2019
- Messages
- 348
- Office Version
- 365
- Platform
- 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:
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:
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.
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 SubThen, 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
'====================================I'm wondering how I can resolve this problem. Please give me a hand. Thank you.
