VBA技巧12-單元格中的數(shù)據有效性

1、精選優(yōu)質文檔傾情為你奉上精選優(yōu)質文檔傾情為你奉上專心專注專業(yè)專心專注專業(yè)精選優(yōu)質文檔傾情為你奉上專心專注專業(yè)單元格中的數(shù)據有效性在單元格中建立數(shù)據有效性在單元格中建立數(shù)據有效性可以使用Add方法，如下面的代碼所示。#001 Sub Validation()#002 With Range(A1:A10).Validation#003 .Delete#004 .Add Type:=xlValidateList, _#005 AlertStyle:=xlValidAlertStop, _#006 Operator:=xlBetween, _#007 Formula1:=1,2,3,4,5,6,7,8

2、#008 End With#009 End Sub代碼解析：使用Add方法在A1：A10單元格中建立數(shù)據有效性。第3行代碼刪除已建立的數(shù)據有效性，防止代碼運行出錯。第4行到第7行代碼使用Add方法建立數(shù)據有效性。應用于Validation對象的Add方法的語法如下：expression.Add(Type, AlertStyle, Operator, Formula1, Formula2)參數(shù)expression是必需的，返回一個Validation對象。參數(shù)Type是必需的，數(shù)據有效性類型。參數(shù)AlertStyl是可選的，有效性檢驗警告樣式。參數(shù)Operator是可選的，數(shù)據有效性運算符。參數(shù)

3、Formula1是可選的，數(shù)據有效性公式的第一部分。參數(shù)Formula2是可選的，當Operator為xlBetween或xlNotBetween時，數(shù)據有效性公式的第二部分（其他情況下，此參數(shù)被忽略）。Add 方法所要求的參數(shù)依有效性檢驗的類型而定，如 REF _Ref h 表格 121所示。數(shù)據有效性類型參數(shù)xlValidateCustomFormula1 必需，忽略 Formula2。Formula1 必須包含一個表達式，數(shù)據項有效時該表達式取值為 True，而數(shù)據項無效時取值為 False。xlInputOnly能使用 AlertStyle、Formula1 或 Formula2 參數(shù)

4、。xlValidateListFormula1 必需，忽略 Formula2。Formula1 必須包含以逗號分隔的取值列表，或引用此列表的工作表。xlValidateWholeNumber、xlValidateDate、xlValidateDecimal、xlValidateTextLength 或 xlValidateTime必須指定 Formula1 或 Formula2 之一，或兩者均指定。表格 STYLEREF 3 s 12 SEQ 表格 * ARABIC s 3 1數(shù)據有效性類型判斷單元格是否存在數(shù)據有效性在VBA中沒有專門的屬性判斷單元格是否存在數(shù)據有效性設置，可以使用Valid

5、ation對象的有效性類型和錯誤陷阱來判斷，如下面的代碼所示。#001 Sub Validation()#002 On Error GoTo Line#003 If Range(A2).Validation.Type = 0 Then#004 MsgBox 單元格有數(shù)據有效性!#005 Exit Sub#006 End If#007 Line:#008 MsgBox 單元格沒有數(shù)據有效性!#009 End Sub代碼解析：Validation過程使用Validation對象的有效性類型和錯誤陷阱來判斷A2單元格中是否存在數(shù)據有效性。第6行代碼，如果A2單元格中存在數(shù)據有效性，Type參數(shù)值就會

6、大于等于0，否則就會發(fā)生錯誤，使用On Error GoTo捕捉到錯誤后轉移到第8行代碼，顯示一個消息框。動態(tài)的數(shù)據有效性利用VBA可以在單元格中建立動態(tài)的數(shù)據有效性，如下面的代碼所示。#001 Private Sub Worksheet_SelectionChange(ByVal Target As Range)#002 If Target.Column = 1 And Target.Count = 1 And Target.Row 1 Then#003 With Target.Validation#004 .Delete#005 .Add Type:=xlValidateList, _#0

7、06 AlertStyle:=xlValidAlertStop, _#007 Operator:=xlBetween, _#008 Formula1:=主機,顯示器#009 End With#010 End If#011 End Sub#012 Private Sub Worksheet_Change(ByVal Target As Range)#013 If Target.Column = 1 And Target.Row 1 And Target.Count = 1 Then#014 With Target.Offset(0, 1).Validation#015 .Delete#016 S

8、elect Case Target#017 Case 主機#018 .Add Type:=xlValidateList, _#019 AlertStyle:=xlValidAlertStop, _#020 Operator:=xlBetween, _#021 Formula1:=Z286,Z386,Z486,Z586#022 Case 顯示器#023 .Add Type:=xlValidateList, _#024 AlertStyle:=xlValidAlertStop, _#025 Operator:=xlBetween, _#026 Formula1:=三星17,飛利浦15,三星15,飛

9、利浦17#027 End Select#028 End With#029 End If#030 End Sub代碼解析：第1行到第11行代碼，工作表的SelectionChange事件，當選擇工作表的A列單元格時，在A2以下的單元格中建立動態(tài)的數(shù)據有效性。其中第2行代碼，利用SelectionChange事件的Target參數(shù)來限制事件的觸發(fā)條件。第3行到第9行代碼使用Add方法在A列單元格中建立數(shù)據有效性。應用于Validation對象的Add方法請參閱技巧 REF _Ref r h 12-1。第12行到第30行代碼，工作表的Change事件，當工作表A列單元格內容改變時，在B列單元格中建立

10、動態(tài)的數(shù)據有效性。其中第16行到第27行代碼，根據A列單元格的內容在B列對應的單元格中建立數(shù)據有效性，其Formula1參數(shù)的值根據A列單元格的內容而變化，使之達到動態(tài)數(shù)據有效性的效果，如 REF _Ref h 圖 121、 REF _Ref h 圖 122所示。圖 STYLEREF 3 s 12 SEQ 圖 * ARABIC s 3 1動態(tài)數(shù)據有效性1圖 STYLEREF 3 s 12 SEQ 圖 * ARABIC s 3 2動態(tài)數(shù)據有效性2自動展開數(shù)據有效性下拉列表選擇工作表單元格時自動展開數(shù)據有效性的下拉列表，如下面的代碼所示。#001 Private Sub Worksheet_Sel

11、ectionChange(ByVal Target As Range)#002 If Target.Column = 5 Then Application.SendKeys %down#003 End Sub代碼解析：當選擇工作表的E列中有數(shù)據有效性的單元格時使用SendKeys方法發(fā)送Alt+向下鍵，打開數(shù)據有效性的下拉列表。應用于Application對象的SendKeys方法將擊鍵發(fā)送給活動應用程序，語法如下：expression.SendKeys(Keys, Wait)參數(shù)expression是可選的，該表達式返回一個Application對象。參數(shù)Keys是必需的，要發(fā)送的鍵或者組合

12、鍵，以文本方式表示。Keys參數(shù)可以指定任何單個鍵或與Alt、Ctrl 或Shift的組合鍵（或者這些鍵的組合）。每個鍵可用一個或多個字符表示。例如，a 表示字符 a，或者 ENTER 表示 Enter。若要指定在按相應鍵時不會顯示的字符（例如，Enter 或 Tab），請使用如 REF _Ref h 表格 122所列的代碼來表示相應的鍵，表中的每個代碼表示鍵盤上的一個鍵。鍵代碼BackspaceBACKSPACE 或 BSBreakBREAKCaps LockCAPSLOCKClearCLEARDelete 或 DelDELETE 或 DELEndENDEnter（波形符）Enter（數(shù)字小鍵盤）ENTEREscESCAPE 或 ESC