根据上述例子和在其他网站上找到的例子，我创建了一个通用的过程和一些示例。
'Simple helper procedure to create a dropdown in a cell based on a list of values in a range
'ValueSheetName : the name of the sheet containing the value range
'ValueRangeString : the range on the sheet with name ValueSheetName containing the values for the dropdown
'CreateOnSheetName : the name of the sheet where the dropdown needs to be created
'CreateInRangeString : the range where the dropdown needs to be created
'FieldName As String : a name of the dropdown, will be used in the inputMessage and ErrorMessage
'See example below ExampleCreateDropDown
Public Sub CreateDropDown(ValueSheetName As String, ValueRangeString As String, CreateOnSheetName As String, CreateInRangeString As String, FieldName As String)
Dim ValueSheet As Worksheet
Set ValueSheet = Worksheets(ValueSheetName) 'The sheet containing the values
Dim ValueRange As Range: Set ValueRange = ValueSheet.Range(ValueRangeString) 'The range containing the values
Dim CreateOnSheet As Worksheet
Set CreateOnSheet = Worksheets(CreateOnSheetName) 'The sheet containing the values
Dim CreateInRange As Range: Set CreateInRange = CreateOnSheet.Range(CreateInRangeString)
Dim InputTitle As String: InputTitle = "Please Select a Value"
Dim InputMessage As String: InputMessage = "for " & FieldName
Dim ErrorTitle As String: ErrorTitle = "Please Select a Value"
Dim ErrorMessage As String: ErrorMessage = "for " & FieldName
Dim ShowInput As Boolean: ShowInput = True 'Show input message on hover
Dim ShowError As Boolean: ShowError = True 'Show error message on error
Dim ValidationType As XlDVType: ValidationType = xlValidateList
Dim ValidationAlertStyle As XlDVAlertStyle: ValidationAlertStyle = xlValidAlertStop 'Stop on invalid value
Dim ValidationOperator As XlFormatConditionOperator: ValidationOperator = xlEqual 'Value must be equal to one of the Values from the ValidationFormula1
Dim ValidationFormula1 As Variant: ValidationFormula1 = "=" & ValueSheetName & "!" & ValueRange.Address 'Formula referencing the values from the ValueRange
Dim ValidationFormula2 As Variant: ValidationFormula2 = ""
Call CreateDropDownWithValidationInCell(CreateInRange, InputTitle, InputMessage, ErrorTitle, ErrorMessage, ShowInput, ShowError, ValidationType, ValidationAlertStyle, ValidationOperator, ValidationFormula1, ValidationFormula2)
End Sub
'An example using the ExampleCreateDropDown
Private Sub ExampleCreateDropDown()
Call CreateDropDown(ValueSheetName:="Test", ValueRangeString:="C1:C5", CreateOnSheetName:="Test", CreateInRangeString:="B1", FieldName:="test2")
End Sub
'The full option function if you need more configurable options
'To create a dropdown in a cell based on a list of values in a range
'Validation: https://msdn.microsoft.com/en-us/library/office/ff840078.aspx
'ValidationTypes: XlDVType https://msdn.microsoft.com/en-us/library/office/ff840715.aspx
'ValidationAlertStyle: XlDVAlertStyle https://msdn.microsoft.com/en-us/library/office/ff841223.aspx
'XlFormatConditionOperator https://msdn.microsoft.com/en-us/library/office/ff840923.aspx
'See example below ExampleCreateDropDownWithValidationInCell
Public Sub CreateDropDownWithValidationInCell(CreateInRange As Range, _
Optional InputTitle As String = "", _
Optional InputMessage As String = "", _
Optional ErrorTitle As String = "", _
Optional ErrorMessage As String = "", _
Optional ShowInput As Boolean = True, _
Optional ShowError As Boolean = True, _
Optional ValidationType As XlDVType = xlValidateList, _
Optional ValidationAlertStyle As XlDVAlertStyle = xlValidAlertStop, _
Optional ValidationOperator As XlFormatConditionOperator = xlEqual, _
Optional ValidationFormula1 As Variant = "", _
Optional ValidationFormula2 As Variant = "")
With CreateInRange.Validation
.Delete
.Add Type:=ValidationType, AlertStyle:=ValidationAlertStyle, Operator:=ValidationOperator, Formula1:=ValidationFormula1, Formula2:=ValidationFormula2
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = InputTitle
.ErrorTitle = ErrorTitle
.InputMessage = InputMessage
.ErrorMessage = ErrorMessage
.ShowInput = ShowInput
.ShowError = ShowError
End With
End Sub
'An example using the CreateDropDownWithValidationInCell
Private Sub ExampleCreateDropDownWithValidationInCell()
Dim ValueSheetName As String: ValueSheetName = "Hidden" 'The sheet containing the values
Dim ValueRangeString As String: ValueRangeString = "C7:C9" 'The range containing the values
Dim CreateOnSheetName As String: CreateOnSheetName = "Test" 'The sheet containing the dropdown
Dim CreateInRangeString As String: CreateInRangeString = "A1" 'The range containing the dropdown
Dim ValueSheet As Worksheet
Set ValueSheet = Worksheets(ValueSheetName)
Dim ValueRange As Range: Set ValueRange = ValueSheet.Range(ValueRangeString)
Dim CreateOnSheet As Worksheet
Set CreateOnSheet = Worksheets(CreateOnSheetName)
Dim CreateInRange As Range: Set CreateInRange = CreateOnSheet.Range(CreateInRangeString)
Dim FieldName As String: FieldName = "Testing Dropdown"
Dim InputTitle As String: InputTitle = "Please Select a value"
Dim InputMessage As String: InputMessage = "for " & FieldName
Dim ErrorTitle As String: ErrorTitle = "Please Select a value"
Dim ErrorMessage As String: ErrorMessage = "for " & FieldName
Dim ShowInput As Boolean: ShowInput = True
Dim ShowError As Boolean: ShowError = True
Dim ValidationType As XlDVType: ValidationType = xlValidateList
Dim ValidationAlertStyle As XlDVAlertStyle: ValidationAlertStyle = xlValidAlertStop
Dim ValidationOperator As XlFormatConditionOperator: ValidationOperator = xlEqual
Dim ValidationFormula1 As Variant: ValidationFormula1 = "=" & ValueSheetName & "!" & ValueRange.Address
Dim ValidationFormula2 As Variant: ValidationFormula2 = ""
Call CreateDropDownWithValidationInCell(CreateInRange, InputTitle, InputMessage, ErrorTitle, ErrorMessage, ShowInput, ShowError, ValidationType, ValidationAlertStyle, ValidationOperator, ValidationFormula1, ValidationFormula2)
End Sub
