excel - 带有数据验证列表的组合框
问题描述
我按照本指南制作了一个组合框,用数据验证列表替换了单元格的常用选择菜单。但是,它总是显示为空白。我的数据验证看起来像这样:=Sheet!$L$2:INDEX(Sheet!$L$2:$L$80;SUMPRODUCT(--(Sheet!$L$2:$L$80<>"")))
我粘贴的代码是这样的:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
On Error GoTo errHandler
If Target.Count > 1 Then GoTo exitHandler
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
On Error GoTo errHandler
If Target.Validation.Type = 3 Then
'if the cell contains a data validation list
Application.EnableEvents = False
'get the data validation formula
str = Target.Validation.Formula1
str = Right(str, Len(str) - 1)
With cboTemp
'show the combobox with the list
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 15
.Height = Target.Height + 5
.ListFillRange = ws.Range(str).Address
.LinkedCell = Target.Address
End With
cboTemp.Activate
'open the drop down list automatically
Me.TempCombo.DropDown
End If
exitHandler:
Application.ScreenUpdating = True
Application.EnableEvents = True
Exit Sub
errHandler:
Resume exitHandler
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
说清楚:该框出现和消失,只是没有任何条目。我不确定错误在哪里,但我想也许我的验证太复杂了?
解决方案
推荐阅读
- node.js - 在节点js中将缓冲区转换为文件的正确方法
- javascript - 在 Carousal 图像滑块中,照片仅在单击点时出现
- android-security - 广播接收器安全问题
- python - 具有从数据框 pandas 中的两列构建的线性回归模型的 fillna
- python - Pytorch torch.load ModuleNotFoundError:没有名为“utils”的模块
- python - nginx + uwsgi + flask + selenium Selenium 驱动程序未打开
- swift - RealmSwift 不保存属性
- android - Google Play 控制台显示我的应用获得了后台位置权限,但我的应用没有
- vue.js - Vuejs上下文中chartjs的闪烁图表和getcontext错误
- java - 当请求没有匹配的 API 时,为什么我的控制器调用不同的 API?