如何选择下拉单元格验证值 [英] How to select a dropdown cell validation value

问题描述

我正在尝试在Excel工作表的数据验证中选择一个值.

I'm attempting to select a value within a data validation in an Excel sheet.

Set r = Sheets("DASHBOARD").Cells.Find(What:="Selection Location ->", LookAt:=xlWhole, searchorder:=xlByColumns, MatchCase:=False, SearchFormat:=False)
r.Offset(0, 1).Select
With Selection.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
      xlBetween, Formula1:="=List1"
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
End With

我的"List1"来自上一行定义的范围内的 Names.Add .
现在已经创建了此列表，我希望Excel在数据验证中选择并显示新的第一个值，或者我在代码中选择的任何值.

My "List1" comes from a Names.Add within a range defined in the lines above.
Now that this list is created, I want Excel to select and display the new first value in my data validation, or any value I so choose in the code.

我无法通过VBA访问任何数据验证.
我可以在 target.cell 中复制/粘贴我想要的值，但是随后数据验证消失了.

I can't find anything on accessing a data validation through VBA.
I can copy/paste the value I would like in the target.cell, however then the data validation disappears.

推荐答案

我刚刚注意到了这个问题，它使我想起了我最近做过的一个项目，其中很长的站点"验证列表被区域"细分了以减少用户要做的滚动量.与您的问题相关的是–一旦选择了一个区域，就会用代码规定一个新的验证列表，并显示该列表上的第一个新值.

I’ve just noticed this question, and it reminds of a project I did recently where a very long validation list of ‘sites’ was broken down by ‘regions’ to reduce the amount of scrolling a user had to do. The relevance to your question is that – once a region had been selected , then a new validation list is stipulated by the code and the first new value on that list is displayed.

我已将相关的代码行标记为:< -THIS LINE".您应该能够调整代码以适合您的需求.如果这不是您想要的，请发表评论，以便我删除答案.

I’ve marked the relevant line of code thus: "<-THIS LINE". You should be able to tweak the code to suit your needs. If this isn’t what you were after, just comment so I can delete the answer.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyList As String
If Not Intersect(Range("L3"), Target) Is Nothing Then

 With Sheet3.Range("L4:N4")  'These are merged cells 
    .ClearContents
    .Validation.Delete
        MyList = Sheet3.Range("L3").Value
    .Validation.Add Type:=xlValidateList, Formula1:="=" & MyList
End With

Sheet3.Buttons("Button1").Caption = MyList

With Sheet3.Range("L4")
    .Value = Sheet4.Range(MyList).Cells(1, 1).Value  '<-THIS LINE
End With

End If
End Sub

这篇关于如何选择下拉单元格验证值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！