VBA常用技巧代码解析

hbcbob

This kind of explanation was truly professional. Thanks indeed.

yuanzhuping

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

1. #001  Public Ctl As CommandBarControl
   
2. #002  Sub DelSht()
   
3. #003      Set Ctl = Application.CommandBars.FindControl(ID:=847)
   
4. #004      Ctl.OnAction = "MyDelSht"
   
5. #005  End Sub
   
6. #006  Sub ResSht()
   
7. #007      Set Ctl = Application.CommandBars.FindControl(ID:=847)
   
8. #008      Ctl.OnAction = ""
   
9. #009  End Sub
   
10. #010  Sub MyDelSht()
    
11. #011      If VBA.UCase$(ActiveSheet.CodeName) = "SHEET2" Then
    
12. #012          MsgBox "禁止删除" & ActiveSheet.Name & "工作表!"
    
13. #013      Else
    
14. #014          ActiveSheet.Delete
    
15. #015      End If
    
16. #016  End Sub

复制代码

代码解析：
       DelSht过程将工作表标签右键菜单中的“删除工作表”菜单的OnAction属性设置为“MyDelSht”。
       第3行代码使用Set语句将工作表标签右键菜单中的“删除工作表”菜单赋给变量Ctl，并将其OnAction属性设置为MyDelSht过程，该菜单被单击时将运行“MyDelSht”过程而不是系统默认的设置。OnAction属性返回或设置一个VBA的过程名，该过程在用户单击或更改某命令栏控件的值时运行。
       ResSht过程将工作表标签右键菜单中的“删除工作表”菜单的OnAction属性恢复为默认设置。
       MyDelSht过程判断所要删除的工作表的代码名称是否是“SHEET2”，如果是则禁止删除该表而只显示一个提示消息框。
       为了不影响其他工作簿的使用，在VBE中双击ThisWorkbook写入下面的代码。

1. #001  Private Sub Workbook_Activate()
   
2. #002      Call DelSht
   
3. #003  End Sub
   
4. #004  Private Sub Workbook_Deactivate()
   
5. #005      Call ResSht
   
6. #006  End Sub

复制代码

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


[ 本帖最后由 yuanzhuping 于 2009-2-16 22:44 编辑 ]

yuanzhuping

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

1. #001  Private Sub Worksheet_Activate()
   
2. #002      Dim sh As Worksheet
   
3. #003      Dim a As Integer
   
4. #004      Dim R As Integer
   
5. #005      R = Sheet1.[A65536].End(xlUp).Row
   
6. #006      a = 2
   
7. #007      If Sheet1.Cells(2, 1) <> "" Then
   
8. #008          Sheet1.Range("A2:A" & R).ClearContents
   
9. #009      End If
   
10. #010      For Each sh In Worksheets
    
11. #011          If sh.CodeName <> "Sheet1" Then
    
12. #012              Sheet1.Cells(a, 1).Value = sh.Name
    
13. #013              a = a + 1
    
14. #014          End If
    
15. #015      Next
    
16. #016  End Sub

复制代码

代码解析：
       工作表的Activate事件，在“目录”工作表激活时自动建立工作簿中除“目录”工作表外所有工作表的目录。
       第2、3、4行代码声明变量类型。
       第5行代码取得A列最后非空单元格的行号。
       第6行代码设置变量a的初始值为2，从A2单元格开始建立工作表目录。
       第7行到第9行代码判断是否存在工作表目录，如果存在先清空原来的目录，以便更新目录。
       第10行到第15代码遍历工作簿的所有工作表，将除“目录”工作表外所有工作表的名称写入到A列单元格中。
       为了建立到各工作表的链接，使用工作表的SelectionChange事件，如下面的代码所示。

1. #001  Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   
2. #002      Dim R As Integer
   
3. #003      R = Sheet1.[A65500].End(xlUp).Row
   
4. #004      On Error Resume Next
   
5. #005      If Target.Count = 1 Then
   
6. #006          If Target.Column = 1 Then
   
7. #007              If Target.Row > 1 And Target.Row <= R Then
   
8. #008                  Sheets(Target.Value).Select
   
9. #009              End If
   
10. #010          End If
    
11. #011      End If
    
12. #012  End Sub

复制代码

代码解析：
       工作表的SelectionChange事件，当选择A列工作表目录中工作表名称时自动选择该单元格所对应的工作表。
       第5、6、7行代码限制该事件触发的条件。
       第8行代码选择单元格所对应的工作表。
      “目录”工作表激活后自动在A列建立工作簿中除“目录”工作表以外所有表的目录，如图所示。


[ 本帖最后由 yuanzhuping 于 2009-2-16 22:45 编辑 ]

lws

感谢yuanzhuping分享！！！！

wangwentao006

谢谢楼主了，太适合我们这些新手了

lzgyfol

太精彩了，下了慢慢学习

Echo_Gao

找到宝了！！谢谢楼主的分享！

yjlhch

非常感谢,lz太伟大了

yuanzhuping

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

1. #001  Dim sh As Worksheet
   
2. #002  Private Sub Workbook_BeforeClose(Cancel As Boolean)
   
3. #003      Sheet1.Visible = True
   
4. #004      For Each sh In ThisWorkbook.Sheets
   
5. #005          If sh.Name <> "空白" Then
   
6. #006              sh.Visible = xlSheetVeryHidden
   
7. #007          End If
   
8. #008      Next
   
9. #009      ActiveWorkbook.Save
   
10. #010  End Sub
    
11. #011  Private Sub Workbook_Open()
    
12. #012      For Each sh In ThisWorkbook.Sheets
    
13. #013          If sh.Name <> "空白" Then
    
14. #014              sh.Visible = xlSheetVisible
    
15. #015          End If
    
16. #016      Next
    
17. #017      Sheet1.Visible = xlSheetVeryHidden
    
18. #018  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工程保护的配合。


[ 本帖最后由 yuanzhuping 于 2009-2-16 22:46 编辑 ]

yuanzhuping

技巧29         防止更改工作表的名称
       工作表的名称显示在工作表标签上，除了在相应的功能菜单中可以对其进行重命名操作外，在工作表标签上双击鼠标也能修改工作表名称。一旦修改了工作表名称，可能就会产生一连串的问题，例如在其他工作簿中对该工作表的引用将会失效，通过工作表名称引用工作表的代码也将出错。
       Excel没有提供修改工作表名称的相关事件，要禁止用户修改工作表名称，需采取其他一些技巧。比如在工作表BeforeClose事件中检验工作表名称，如果工作表名称不是指定的字符串，则将其修改为指定字符串，即保持工作表名称不变，代码如下。

1. #001  Private Sub Workbook_BeforeClose(Cancel As Boolean)
   
2. #002      If Sheet1.Name <> "Excel Home" Then Sheet1.Name = "Excel Home"
   
3. #003      ThisWorkbook.Save
   
4. #004  End Sub

复制代码

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

[ 本帖最后由 yuanzhuping 于 2009-2-16 22:47 编辑 ]