之前使用列表控件实现对单元格内容的选择,前两天发现数据有效性也可以提供一个列表框,上午改了一下,用起来还不错,但感觉不如原来的舒服,还是改回来吧,把数据有效性的函数粘贴到这吧,以备不时之需.

使用方法如下

Sub Test()
    Call SetDataValidation(Range(Cells(2, 1), Cells(9, 1)), "y")   
End Sub

 

' 设置Status数据有效性
Sub SetDataValidation(ByVal RangeObj As Range, ByVal flag As String)
If flag = "y" Then
    With RangeObj.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertWarning, Operator _
        :=xlBetween, Formula1:="未安排,已安排,已完成,提醒"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = "Tip"
        .ErrorTitle = "Warning"
        .InputMessage = "修改任务的Status"
        .ErrorMessage = "输入的数据非有效数据，是否确定输入内容？"
        .IMEMode = xlIMEModeNoControl
        .ShowInput = True
        .ShowError = True
    End With
    RangeObj.Value = "未安排"
ElseIf flag = "n" Then
    With RangeObj.Validation
        .Delete
        .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
        :=xlBetween
        .IgnoreBlank = True
        .InCellDropdown = True
        .IMEMode = xlIMEModeNoControl
        .ShowInput = True
        .ShowError = True
    End With
    RangeObj.Value = ""
End If
End Sub

效果如下

如果自己输入内容,而输入的内容还不对,则会蹦出如下对话框