《Excel VBA跟卢子一起学早做完，不加班(实战进阶版)》

《Excel VBA跟卢子一起学早做完，不加班(实战进阶版)》_3.12 新建表时触发事件

【摘要】本书摘自《Excel VBA跟卢子一起学早做完，不加班(实战进阶版)》一书中第3章，第12节，陈锡卢、李应钦著。

3.12 新建表时触发事件: Wortbook(kaSheet和Worbook Na.Chrt

无言：现在讲关于Workbook.NewSheet工作簿事件。

Workbook.NewSheet事件过程的作用是当新建工作表或图表时都能触发该事件，通过该事件，在新建表时按需设置且一步到位。该事件过程外壳语法如下。

当在工作簿中新建工作表时发生此事件

《Excel VBA跟卢子一起学早做完，不加班(实战进阶版)》_3.12 新建表时触发事件

Private Sub Workbook_NewSheet(By Val Sh As Object)

Statements(中间代码语句)

End Sub

Workbook.NewSheet 事件只有一个 Sh 参数，该参数主要用于判断新建的表类型是 Worksheet 或 Chart 对象。

无言：现在利用Workbook.NewSheet事件过程设置一个智能新表，当插入表类型为Worksheet 时设置好表页面、页脚等信息，按当天日期命名表的标签名称等。具体如代码3-26所示。

代码3-26 新建表时设置新表格式

001|Private Sub Workbook_NewSheet(By Val Sh As Object)

002| If Sh.Type =xlWorksheet Then

003| Application.ScreenUpdating =False

004| With Sh

005| Move After:=Worksheets(Sheets.Count)

006| .Name =Format(Date,"yyyymmdd日产记录表")

007| With.Cells(1)

008| .Resize(1,10).Merge

009| .Value =Format(Date,"yyyy年mm月dd日日产记录表")

010| Font.Bold=True.Font.Size=20:.RowHeight=35'

011| .HorizontalAlignment =xICenter:.VerticalAlignment =xICenter

012| With.Offset(1).Resize(1,10)

013

.Value=Array("序号","日期","班组”,"班组负责人”,"型号规格”,"数量","单价","小

计","备注","统计员")

.HorizontalAlignment =xlCenter:.VerticalAlignment =xICenter

End With

With .Offset(2)

.Value =1

.Resize(50).DataSeries Rowcol:=xIColumns,Type:=xILinear,Date:=xIDay,Step:=1,

stop:=50,Trend:=False

.Resize(50,7).HorizontalAlignment =xICenter

.Resize(50,7),VerticalAlignment =xICenter

.Offset(0,1).Resize(50),.NumberFormat ="yyyy/mm/dd"

.Offset(0,5).Resize(51,3).NumberFormat ="#,##0.00;[Red]-#,##0.00;"

End With

Sh.ListObjects.Add(xlSrcRange,.Offset(1).Resize(51,10),,xlYes).Unlist

.Offset(2).Offset(0,7).Resize(50).FormulaR1C1="=RC[-2]*RC[-1]"

.Offset(52)="合计"

.Offset(1).Offset(52,7)="=SUM(R[3]C:R[-1]C)"

End With

With.Cells(2,1).Resize(52,10)

.Borders.LineStyle=1:RowHeight=17:.ColumnWidth=15:Jtem(1).Resize(1).Columns.AutoFit

End With

Application.PrintCommunication=False

ActiveWindow.View =xIPageBreakPreview

With.PageSetup

.PaperSize =xlPaperA4:PrintArea =Sh.UsedRange.Address

.Orientation =xlLandscape:.PrintTitleRows="$1:S2"

End With

.VPageBreaks(1).DragOff Direction:=xIToRight,RegionIndex:=1

.ScrollArea =.UsedRange.Address

ActiveWindow.View =xINormalView

Application.PrintCommunication=True

Application.ScreenUpdating =True

(1)首先过程通过If Sh.Type=XIWorksheet语句判断新建的表的类似是否为Worksheet表，是则继续执行中间代码，并关闭屏幕刷新提速；接着用Wokrsheet.Move方法将新建的表移动到工作簿的最后，并按照日期+固定字符命名。

( 2 ) 将A1:J1 单元格合并，并写入具体内容，再设置合并单元格字体的相关属性以及单元格的行高及水平和垂直的对齐方式。

(3)设置完抬头后，接着偏移到其下一行位置，用Array函数在A2:J2 单元格区域写入具体的标题内容且设置对齐方式。

(4)继标题设置后从A1 单元偏移2行后在A3 单元写1,并运用Range.DataSeries 方法填充1～50的序号，并设置A3:J52 单元格区域的对齐方式，然后通过.Offset(0,1).Resize(50). NumberFormat 和 .Offset(0,5).Resize(51,3).NumberFormat语句分别设置了日期列的日期格式和 F:H 三列的数字格式。

( 5 ) 设置完了表区域的相关格式后通过Sh.ListObjects.Add(XISrcRange,.Offset(1). Resize(51,10),,XIYes).Unlist 语句创建一个表格后再将其转换为普通单元格区域，作为表格样式代入。

(6)转换后在H 列的合计行写入小计公式和 Sum 合计公式，公式使用R1C1方式写入单元格，这样公式在写入单元格后能自动变化引用的单元格位置；公式写完、样式设置完之后，继续设置A2:J53 区域的边框、行高、列宽，最后将第1列的设置为自动列宽。