VBA技巧12---单元格中的数据有效性

技巧1单元格中的数据有效性1-1在单元格中建立数据有效性在单元格中建立数据有效性可以使用Add方法，如下面的代码所示。#001SubValidation()#002WithRange(A1:A10).Validation#003.Delete#004.AddType:=xlValidateList,_#005AlertStyle:=xlValidAlertStop,_#006Operator:=xlBetween,_#007Formula1:=1,2,3,4,5,6,7,8#008EndWith#009EndSub代码解析：使用Add方法在A1：A10单元格中建立数据有效性。第3行代码删除已建立的数据有效性，防止代码运行出错。第4行到第7行代码使用Add方法建立数据有效性。应用于Validation对象的Add方法的语法如下：expression.Add(Type,AlertStyle,Operator,Formula1,Formula2)参数expression是必需的，返回一个Validation对象。参数Type是必需的，数据有效性类型。参数AlertStyl是可选的，有效性检验警告样式。参数Operator是可选的，数据有效性运算符。参数Formula1是可选的，数据有效性公式的第一部分。参数Formula2是可选的，当Operator为xlBetween或xlNotBetween时，数据有效性公式的第二部分（其他情况下，此参数被忽略）。Add方法所要求的参数依有效性检验的类型而定，如表格1-1所示。数据有效性类型参数xlValidateCustomFormula1必需，忽略Formula2。Formula1必须包含一个表达式，数据项有效时该表达式取值为True，而数据项无效时取值为False。xlInputOnly能使用AlertStyle、Formula1或Formula2参数。xlValidateListFormula1必需，忽略Formula2。Formula1必须包含以逗号分隔的取值列表，或引用此列表的工作表。xlValidateWholeNumber、xlValidateDate、xlValidateDecimal、xlValidateTextLength或xlValidateTime必须指定Formula1或Formula2之一，或两者均指定。表格1-1数据有效性类型1-2判断单元格是否存在数据有效性在VBA中没有专门的属性判断单元格是否存在数据有效性设置，可以使用Validation对象的有效性类型和错误陷阱来判断，如下面的代码所示。#001SubValidation()#002OnErrorGoToLine#003IfRange(A2).Validation.Type=0Then#004MsgBox单元格有数据有效性!#005ExitSub#006EndIf#007Line:#008MsgBox单元格没有数据有效性!#009EndSub代码解析：Validation过程使用Validation对象的有效性类型和错误陷阱来判断A2单元格中是否存在数据有效性。第6行代码，如果A2单元格中存在数据有效性，Type参数值就会大于等于0，否则就会发生错误，使用OnErrorGoTo捕捉到错误后转移到第8行代码，显示一个消息框。1-3动态的数据有效性利用VBA可以在单元格中建立动态的数据有效性，如下面的代码所示。#001PrivateSubWorksheet_SelectionChange(ByValTargetAsRange)#002IfTarget.Column=1AndTarget.Count=1AndTarget.Row1Then#003WithTarget.Validation#004.Delete#005.AddType:=xlValidateList,_#006AlertStyle:=xlValidAlertStop,_#007Operator:=xlBetween,_#008Formula1:=主机,显示器#009EndWith#010EndIf#011EndSub#012PrivateSubWorksheet_Change(ByValTargetAsRange)#013IfTarget.Column=1AndTarget.Row1AndTarget.Count=1Then#014WithTarget.Offset(0,1).Validation#015.Delete#016SelectCaseTarget#017Case主机#018.AddType:=xlValidateList,_#019AlertStyle:=xlValidAlertStop,_#020Operator:=xlBetween,_#021Formula1:=Z286,Z386,Z486,Z586#022Case显示器#023.AddType:=xlValidateList,_#024AlertStyle:=xlValidAlertStop,_#025Operator:=xlBetween,_#026Formula1:=三星17,飞利浦15,三星15,飞利浦17#027EndSelect#028EndWith#029EndIf#030EndSub代码解析：第1行到第11行代码，工作表的SelectionChange事件，当选择工作表的A列单元格时，在A2以下的单元格中建立动态的数据有效性。其中第2行代码，利用SelectionChange事件的Target参数来限制事件的触发条件。第3行到第9行代码使用Add方法在A列单元格中建立数据有效性。应用于Validation对象的Add方法请参阅技巧1-1。第12行到第30行代码，工作表的Change事件，当工作表A列单元格内容改变时，在B列单元格中建立动态的数据有效性。其中第16行到第27行代码，根据A列单元格的内容在B列对应的单元格中建立数据有效性，其Formula1参数的值根据A列单元格的内容而变化，使之达到动态数据有效性的效果，如图1-1、图1-2所示。图1-1动态数据有效性1图1-2动态数据有效性21-4自动展开数据有效性下拉列表选择工作表单元格时自动展开数据有效性的下拉列表，如下面的代码所示。#001PrivateSubWorksheet_SelectionChange(ByValTargetAsRange)#002IfTarget.Column=5ThenApplication.SendKeys%{down}#003EndSub代码解析：当选择工作表的E列中有数据有效性的单元格时使用SendKeys方法发送Alt+向下键，打开数据有效性的下拉列表。应用于Application对象的SendKeys方法将击键发送给活动应用程序，语法如下：expression.SendKeys(Keys,Wait)参数expression是可选的，该表达式返回一个Application对象。参数Keys是必需的，要发送的键或者组合键，以文本方式表示。Keys参数可以指定任何单个键或与Alt、Ctrl或Shift的组合键（或者这些键的组合）。每个键可用一个或多个字符表示。例如，a表示字符a，或者{ENTER}表示Enter。若要指定在按相应键时不会显示的字符（例如，Enter或Tab），请使用如表格1-2所列的代码来表示相应的键，表中的每个代码表示键盘上的一个键。键代码Backspace{BACKSPACE}或{BS}Break{BREAK}CapsLock{CAPSLOCK}Clear{CLEAR}Delete或Del{DELETE}或{DEL}End{END}Enter~（波形符）Enter（数字小键盘）{ENTER}Esc{ESCAPE}或{ESC}F1到F15{F1}到{F15}Help{HELP}Home{HOME}Ins{INSERT}NumLock{NUMLOCK}PageDown{PGDN}PageUp{PGUP}Return{RETURN}ScrollLock{SCROLLLOCK}Tab{TAB}向上键{UP}向下键{DOWN}向右键{RIGHT}向左键{LEFT}表格1-2按键代码