vba 下拉列表中的默认值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14012234/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Default Value From Drop Down List
提问by IRHM
I wonder whether someone may be able to help me please.
我想知道是否有人可以帮助我。
I'm using the code below, which among a number of actions being performed, automatically populates column "A" with the date, and column "AS" with the text value "No" when a new record is created within a Excel spreadsheet.
我正在使用下面的代码，在执行的许多操作中，当在 Excel 电子表格中创建新记录时，它会自动使用日期填充“A”列，并使用文本值“No”填充“AS”列。
Option Explicit
Public preValue As Variant
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range, res As Variant
Dim rCell As Range
Dim Rng1 As Range
Dim Rng2 As Range
Dim Rng3 As Range
Application.EnableCancelKey = xlDisabled
'Sheets("Input").Protect "handsoff", UserInterFaceOnly:=True, AllowFiltering:=True, AllowFormattingColumns:=True
If Target.Column = 3 Then
If Target = "No" Then MsgBox "Please remember to make the same change to all rows for " & Target.Offset(0, -1).Value & " and delete any future forecasts"
End If
If Target.Cells.Count > 1 Then Exit Sub
On Error Resume Next
If Not Intersect(Target, Range("B5:AD400", "AF5:AQ400")) Is Nothing Then
If Target.Value <> preValue And Target.Value <> "" Then
Application.EnableEvents = False
With Rows(Target.Row)
.Range("A1").Value = Date
.Range("AS1").Value = "No"
End With
Application.EnableEvents = True
Target.Interior.ColorIndex = 35
End If
End If
On Error GoTo 0
If Target.Column = 45 Then
If Target.Value = "Yes" Then
Set Rng1 = Application.Union(Cells(Target.Row, "B").Resize(, 19), Cells(Target.Row, "R"))
Rng1.Interior.ColorIndex = xlNone
Set Rng2 = Application.Union(Cells(Target.Row, "S").Resize(, 12), Cells(Target.Row, "AD"))
Rng2.Interior.ColorIndex = 37
Set Rng3 = Application.Union(Cells(Target.Row, "AF").Resize(, 12), Cells(Target.Row, "AQ"))
Rng3.Interior.ColorIndex = 42
End If
End If
If Not Intersect(Target, Range("J7:J400")) Is Nothing Then
Set Cell = Worksheets("Lists").Range("B2:C23")
res = Application.VLookup(Target, Cell, 2, False)
If IsError(res) Then
Range("K" & Target.Row).Value = ""
Else
Range("K" & Target.Row).Value = res
End If
End If
End Sub
What I'd like to do, if at all possible, is when the date is inserted into column "A", I'd like to insert the text value "Select" on the same row in column "C". This value is taken from the first value I have in a drop down menu, set up on a sheet called "Lists" with the named range "RDStaff".
如果可能的话，我想做的是将日期插入到“A”列中时，我想在“C”列的同一行中插入文本值“Select”。该值取自下拉菜单中的第一个值，设置在名为“列表”的工作表上，命名范围为“RDStaff”。
Could someone perhaps tell me please how I may go about changing the functionality, so that as soon as column "A" is populated with the date, the first value from my list i.e. "Select" is automatically populated in column "C"?
也许有人可以告诉我如何更改功能，以便在“A”列填充日期后，我的列表中的第一个值即“选择”会自动填充到“C”列中？
Many thanks and kind regards
非常感谢和亲切的问候
Chris
克里斯
回答by Scott Holtzman
It is not clear exactly which cell in column C is where your validation list is being used from, but if you add the code below into your with statement it should work, of course, adjusting to the appropriate drop-down cell.
目前尚不清楚 C 列中的哪个单元格是从哪里使用您的验证列表，但是如果您将下面的代码添加到 with 语句中，它应该可以工作，当然，调整到适当的下拉单元格。
.Range("C1").Value = Sheets(1).Range("C10").Value
.Range("C1").Value = Sheets(1).Range("C10").Value
Now, this assumes your drop down list, based on your validation is in the first sheet of your workbook (by index) in cell C10. You'll need to adjust these to match your data / workbook structure.
现在，这假设您的下拉列表基于您的验证位于单元格 C10 中工作簿的第一张表（按索引）中。您需要调整这些以匹配您的数据/工作簿结构。
The point is that you don't hard code the value. You reference the value from the drop-down list location.
关键是你没有对值进行硬编码。您从下拉列表位置引用该值。
Per your comments, here is a code snippet to add the validation list into your code.
根据您的评论，这里有一个代码片段，用于将验证列表添加到您的代码中。
With Rows(Target.Row)
'... your existing code
With Range("C1").Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Lists!RDStaff ' you may need to make this named range global for it to work on another sheet in this context
.IgnoreBlank = True
.InCellDropdown = True
End With
End WIth
