|
下面应一位粉丝的要求，开始把我以前的一些学习资料整理了一下，然后分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(2，1) <> ““ Then Sheet1.Range('A2:A' & R).ClearContents EndIf ForEach sh In Worksheets If sh.CodeName <> 'Sheet1'Then Sheet1.Cells(a，1).Value = sh.Name a = a + 1 End If Next End Sub 代码解析： 工作表的Activate事件，在“目录”工作表激活时自动建立工作簿中除“目录”工作表外所有工作表的目录。 第2、3、4行代码声明变量类型。 第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列工作表目录中工作表名称时自动选择该单元格所对应的工作表。 第5、6、7行代码限制该事件触发的条件。 第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，使其可见。 应用于Charts和Worksheets对象的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(Shift，CopyOrigin) 参数expression是必需的，该表达式返回一个Range对象。 参数Shift是可选的，指定单元格的移动方向。可为以下XlInsertShiftDirection常量之一：xlShiftToRight或xlShiftDown。如果省略本参数，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 |
|
|
来自： wdmexcel > 《Excel Vba》
