wdmexcel / Excel Vba / VBA常用代码解析(第五讲)

分享

   

VBA常用代码解析(第五讲)

2015-08-21  wdmexcel



下面应一位粉丝的要求,开始把我以前的一些学习资料整理了一下,然后分20期发给各位有需要的朋友,您也可以索取源文件,邮箱:546039945@qq.com!

026 禁止删除指定工作表

在工作表事件中是没有工作表删除事件的,为了防止用户误删除重要的工作表,除了使用保护工作簿方法外,还可以使用下面的代码。

Public Ctl As CommandBarControl

Sub DelSht()

SetCtl = Application.CommandBars.FindControl(ID:=847)

Ctl.OnAction= 'MyDelSht'

End Sub

Sub ResSht()

SetCtl = Application.CommandBars.FindControl(ID:=847)

Ctl.OnAction= ““

End Sub

Sub MyDelSht()

If VBA.UCase$(ActiveSheet.CodeName)= 'SHEET2' Then

MsgBox '禁止删除' & ActiveSheet.Name & '工作表!'

Else

ActiveSheet.Delete

EndIf

End Sub

代码解析:

DelSht过程将工作表标签右键菜单中的“删除工作表”菜单的OnAction属性设置为“MyDelSht”。

3行代码使用Set语句将工作表标签右键菜单中的“删除工作表”菜单赋给变量Ctl,并将其OnAction属性设置为MyDelSht过程,该菜单被单击时将运行“MyDelSht”过程而不是系统默认的设置。OnAction属性返回或设置一个VBA的过程名,该过程在用户单击或更改某命令栏控件的值时运行。

ResSht过程将工作表标签右键菜单中的“删除工作表”菜单的OnAction属性恢复为默认设置。

MyDelSht过程判断所要删除的工作表的代码名称是否是“SHEET2,如果是则禁止删除该表而只显示一个提示消息框。

为了不影响其他工作簿的使用,在VBE中双击ThisWorkbook写入下面的代码。

Private Sub Workbook_Activate()

CallDelSht

End Sub

Private Sub Workbook_Deactivate()

CallResSht

End Sub

代码解析:

工作簿的Activate事件和Deactivate事件代码,在工作簿激活时运行DelSht过程,在关闭或打开其他工作簿时运行ResSht过程,这样只禁止删除本工作簿中“SHEET2”工作表,并不影响其他工作簿。当删除本工作簿中的“SHEET2”工作表时,并不会执行删除工作表操作而只会显示禁止删除工作表的消息框。

027 自动建立工作表目录

如果在工作簿中有许多工作表,使用时往往会建立一张目录表并插入超链接以方便选择工作表。但是如果工作簿中的工作表经常添加和删除,使用手工建立目录很不方便,此时可以使用工作表的Activate事件自动建立工作表的目录,如下面的代码所示。

Private Sub Worksheet_Activate()

Dimsh As Worksheet

Dima As Integer

DimR As Integer

R =Sheet1.[A65536].End(xlUp).Row

a =2

If Sheet1.Cells(21) <> ““ Then

Sheet1.Range('A2:A' & R).ClearContents

EndIf

ForEach sh In Worksheets

If sh.CodeName <> 'Sheet1'Then

Sheet1.Cells(a1).Value = sh.Name

a = a + 1

End If

Next

End Sub

代码解析:

工作表的Activate事件,在“目录”工作表激活时自动建立工作簿中除“目录”工作表外所有工作表的目录。

234行代码声明变量类型。

5行代码取得A列最后非空单元格的行号。

6行代码设置变量a的初始值为2,从A2单元格开始建立工作表目录。

7行到第9行代码判断是否存在工作表目录,如果存在先清空原来的目录,以便更新目录。

10行到第15代码遍历工作簿的所有工作表,将除“目录”工作表外所有工作表的名称写入到A列单元格中。

为了建立到各工作表的链接,使用工作表的SelectionChange事件,如下面的代码所示。

Private Sub Worksheet_SelectionChange(ByVal TargetAs Range)

DimR As Integer

R =Sheet1.[A65500].End(xlUp).Row

On ErrorResume Next

If Target.Count= 1 Then

If Target.Column = 1 Then

If Target.Row > 1 And Target.Row<= R Then

Sheets(Target.Value).Select

End If

End If

EndIf

End Sub

代码解析:

工作表的SelectionChange事件,当选择A列工作表目录中工作表名称时自动选择该单元格所对应的工作表。

567行代码限制该事件触发的条件。

8行代码选择单元格所对应的工作表。

“目录”工作表激活后自动在A列建立工作簿中除“目录”工作表以外所有表的目录,

028 工作表的深度隐藏

在使用VBA开发的工作簿文件完成交与用户使用后,我们往往希望用户在打开工作簿时启用宏,此时除了使用“禁用宏则关闭工作簿”的功能外,还可以隐藏所有有数据的工作表,如果用户在打开工作簿时禁用宏则只显示一张空白的工作表,达到强制启用宏的效果,代码如下:

Dim sh As Worksheet

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Sheet1.Visible= True

ForEach sh In ThisWorkbook.Sheets

If sh.Name <> '空白' Then

sh.Visible = xlSheetVeryHidden

End If

Next

ActiveWorkbook.Save

End Sub

Private Sub Workbook_Open()

ForEach sh In ThisWorkbook.Sheets

If sh.Name <> '空白' Then

sh.Visible = xlSheetVisible

End If

Next

Sheet1.Visible= xlSheetVeryHidden

End Sub

代码解析:

2行到第10行代码是工作簿的BeforeClose事件过程,在工作簿关闭前隐藏除“空白”表以外的所有的工作表。

3行代码将“空白”表的Visible属性设置为True,使其可见。

应用于ChartsWorksheets对象的Visible属性决定对象是否可见,语法如下:

expression.Visible

参数expression是必需的,该表达式返回上面的对象之一。

Visible属性可以设置为表格所示的XlSheetVisibility常量之一。

4行到第8行代码使用For Each...Next语句遍历工作簿中所有的工作表,将除“空白”表以外的所有工作表的Visible属性设置为xlSheetVeryHidden,使之隐藏。

Visible属性设置为xlSheetVeryHidden后工作表不能通过“格式”→“工作表”→“取消隐藏”菜单来显示隐藏的工作表。

9行代码使用Save方法保存代码所在工作簿的更改,在关闭工作簿时不显示消息框。

10行到第18行代码是工作簿的Open事件过程,在打开工作簿时将除“空白”表以外的所有工作表的Visible属性设置为xlSheetVisible,取消隐藏。如果打开工作簿时禁用宏,则工作簿中除了“空白”表以外,其他的工作表还处于深度隐藏的状态,,这样就达到强制用户启用宏的效果,当然这还需要VBA工程保护的配合。

029 防止更改工作表的名称

工作表的名称显示在工作表标签上,除了在相应的功能菜单中可以对其进行重命名操作外,在工作表标签上双击鼠标也能修改工作表名称。一旦修改了工作表名称,可能就会产生一连串的问题,例如在其他工作簿中对该工作表的引用将会失效,通过工作表名称引用工作表的代码也将出错。

Excel没有提供修改工作表名称的相关事件,要禁止用户修改工作表名称,需采取其他一些▲。比如在工作表BeforeClose事件中检验工作表名称,如果工作表名称不是指定的字符串,则将其修改为指定字符串,即保持工作表名称不变,代码如下。

Private Sub Workbook_BeforeClose(Cancel As Boolean)

If Sheet1.Name<> 'Excel Home' Then Sheet1.Name = 'Excel Home'

ThisWorkbook.Save

End Sub

代码解析:

工作簿的BeforeClose事件过程,在关闭当前工作簿时判断Sheet1工作表名称,如果不是指定的字符串“Excel Home”,则将其恢复为“ExcelHome”后保存工作簿,从而避免更改Sheet1工作表名称。

030 工作表中一次插入多行

在工作表的中插入多行空行,需要使用Insert方法,如下面的代码所示。

Sub InSertRows_1()

Dimi As Integer

Fori = 1 To 3

Sheet1.Rows(3).Insert

Next

End Sub

代码解析:

nSertRows_1过程使用Insert方法在数据区域的第2行和第3行之间插入三行空行。

Insert方法应用于Range对象时在工作表或宏表中插入一个单元格或单元格区域,其他单元格作相应移位以腾出空间,语法如下:

expression.Insert(ShiftCopyOrigin)

参数expression是必需的,该表达式返回一个Range对象。

参数Shift是可选的,指定单元格的移动方向。可为以下XlInsertShiftDirection常量之一:xlShiftToRightxlShiftDown。如果省略本参数,MicrosoftExcel将依据该区域的形状决定移动方向。

参数CopyOrigin是可选的,复制的起点。

还可以使用引用多行的方法,如下面的代码所示。

Sub InSertRows_2()

Sheet2.Range('A3').EntireRow.Resize(3).Insert

End Sub

代码解析:

InSertRows_2过程通过引用多行区域的方法实现一次插入多行。

2行代码中的Range(A3).EntireRow属性返回Range(A3)单元格所在的一整行,然后使用Resize属性调整行数后插入三行空行。

也可以直接指定相应行再调整行数后插入空行,如下面的示例代码:

Sub InSertRows_3()

Sheet3.Rows(3).Resize(3).Insert

End Sub


    本站是提供个人知识管理的网络存储空间,所有内容均由用户发布,不代表本站观点。请注意甄别内容中的联系方式、诱导购买等信息,谨防诈骗。如发现有害或侵权内容,请点击一键举报。

    0条评论

    发表

    请遵守用户 评论公约

    类似文章 更多
    喜欢该文的人也喜欢 更多

    ×
    ×

    ¥.00

    微信或支付宝扫码支付:

    开通即同意《个图VIP服务协议》

    全部>>