第11章 vba代码

《第11章 vba代码》由会员上传分享，免费在线阅读，更多相关内容在学术论文-天天文库。

1、第11章综合实例范例153员工花名册PrivateSubWorksheet_SelectionChange(ByValTargetAsRange)DimrAsIntegerr=Sheet1.Cells(Sheet1.Rows.Count,2).End(xlUp).RowWithTargetIf.Count=1And.Row>5And.Row<=rThenSheet1.UnprotectSelectCase.ColumnCase7With.Validation.Delete.Add3,1,1,"高级会计师,会计师,助理会计师,会计员,"

2、_&"高级工程师,工程师,助理工程师,技术员,"_&"高级经济师,经济师,助理经济师,无"EndWithCase8With.Validation.Delete.Add3,1,1,"经理室,办公室,行政科,生技科,"_&"财务科,营业部,其他,退休"EndWithCase9With.Validation.Delete.Add3,1,1,"经理,副经理,中层正职,中层副职,"_&"总账会计,辅助会计,出纳会计,驾驶员,"_&"办事员,收费员,发货员,采购员,化验员,"_&"班组长,电工,中控值班,制水工,外借,内退"EndWithCase

3、10With.Validation.Delete.Add3,1,1,"在职,内退,退休"EndWithCaseElseEndSelect155Sheet1.ProtectEndIfEndWithEndSubPrivateSubWorksheet_Change(ByValTargetAsRange)Sheet1.UnprotectWithTargetIf.Count=1And.Row>5ThenIf.Column=2AndLen(.Text)<>0ThenRange(Cells(.Row,1),Cells(.Row,10)).Borde

4、rs.LineStyle=xlContinuous.Offset(0,-1).FormulaR1C1="=ROW()-5"EndIfIf.Column=6ThenApplication.EnableEvents=FalseSelectCaseLen(.Text)Case18.Offset(0,-5).FormulaR1C1="=ROW()-5".Offset(0,-3)=IIf(Mid(.Text,17,1)Mod2=0,"女","男").Offset(0,-2)=Format(Mid(.Text,7,8),"#-00-00").Of

5、fset(0,-1).FormulaR1C1="=DATEDIF(TEXT(MID(RC[1],7,8),""#-00-00""),TODAY(),""y"")"Case0Range(Cells(.Row,3),Cells(.Row,6))=""CaseElse.SelectMsgBox"身份证号码不正确,请重新输入!",64,"提示"EndSelectApplication.EnableEvents=TrueEndIfEndIfEndWithSheet1.ProtectEndSub="合计"&COUNTA(B:B)-1&"人男"&C

6、OUNTIF(C:C,"男")&"人女"&COUNTIF(C:C,"女")&"人"="在职"&COUNTIF(J:J,"在职")&"人内退"&COUNTIF(J:J,"内退")&"人退休"&COUNTIF(J:J,"退休")&"人SubSectorSort()DimrAsIntegerWithSheet1.Unprotectr=.Cells(.Rows.Count,2).End(xlUp).Row155.Range("A6:J"&r).SortKey1:=.Range("H6"),_Order1:=xlAscending,Key2:=

7、Range("D6"),_OrderCustom:=13.ProtectEndWithEndSubSubAgeSort()DimrAsIntegerDimMymsgAsIntegerWithSheet1r=.Cells(.Rows.Count,2).End(xlUp).Row.UnprotectMymsg=MsgBox("选择""是""按升序排序,选择""否""按降序排序",vbYesNoCancel)SelectCaseMymsgCase6.Range("A6:J"&r).SortKey1:=.Range("E6"),_Order1

8、:=xlAscending,Key2:=.Range("D6")Case7.Range("A6:J"&r).SortKey1:=.Range("E6"),_Order1:=xlDescending,Key2:=.Rang