根据其它字段的值来决定自己是否为下拉框

'BP Type为Dist时，Product/Metric列为下拉列表1、Shipped REV ($k), 2、Sell Thru Units, 3、WOS
'否则还是普通的文本框
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'首先判断被选择的是不是Product/Metric，并且判断BP Type的值是不是Dist
If Target.Column = 6 And Cells(Target.Row, 4).Value = "3 Dist" Then
'如果是，则将Product/Metric设为下拉框，并添加数据
   With Cells(Target.Row, 6).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="1、Shipped REV ($k), 2、Sell Thru Units, 3、WOS"
        .IgnoreBlank = False
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .IMEMode = xlIMEModeNoControl
        .ShowInput = True
        .ShowError = True
    End With
Else
'如果不是，则将Product/Metric置为普通文本框
     With Cells(Target.Row, 6).Validation
        .Delete
        .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
        :=xlBetween
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .IMEMode = xlIMEModeNoControl
        .ShowInput = True
        .ShowError = True
    End With
End If
End Sub

IF 和Else里的代码表示把某列设为下拉框或普通文本框，是通过录制宏的方式得到的，比较简单