下面應(yīng)一位粉絲的要求，開始把我以前的一些學(xué)習(xí)資料整理了一下，然后分20期發(fā)給各位有需要的朋友，您也可以索取源文件，郵箱：546039945@qq.com！
026 禁止刪除指定工作表
在工作表事件中是沒有工作表刪除事件的，為了防止用戶誤刪除重要的工作表，除了使用保護(hù)工作簿方法外，還可以使用下面的代碼。
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過程將工作表標(biāo)簽右鍵菜單中的“刪除工作表”菜單的OnAction屬性設(shè)置為“MyDelSht”。
第3行代碼使用Set語句將工作表標(biāo)簽右鍵菜單中的“刪除工作表”菜單賦給變量Ctl，并將其OnAction屬性設(shè)置為MyDelSht過程，該菜單被單擊時(shí)將運(yùn)行“MyDelSht”過程而不是系統(tǒng)默認(rèn)的設(shè)置。OnAction屬性返回或設(shè)置一個(gè)VBA的過程名，該過程在用戶單擊或更改某命令欄控件的值時(shí)運(yùn)行。
ResSht過程將工作表標(biāo)簽右鍵菜單中的“刪除工作表”菜單的OnAction屬性恢復(fù)為默認(rèn)設(shè)置。
MyDelSht過程判斷所要?jiǎng)h除的工作表的代碼名稱是否是“SHEET2”，如果是則禁止刪除該表而只顯示一個(gè)提示消息框。
為了不影響其他工作簿的使用，在VBE中雙擊ThisWorkbook寫入下面的代碼。
Private Sub Workbook_Activate()
CallDelSht
End Sub
Private Sub Workbook_Deactivate()
CallResSht
End Sub
代碼解析：
工作簿的Activate事件和Deactivate事件代碼，在工作簿激活時(shí)運(yùn)行DelSht過程，在關(guān)閉或打開其他工作簿時(shí)運(yùn)行ResSht過程，這樣只禁止刪除本工作簿中“SHEET2”工作表，并不影響其他工作簿。當(dāng)刪除本工作簿中的“SHEET2”工作表時(shí)，并不會(huì)執(zhí)行刪除工作表操作而只會(huì)顯示禁止刪除工作表的消息框。
▲027 自動(dòng)建立工作表目錄
如果在工作簿中有許多工作表，使用時(shí)往往會(huì)建立一張目錄表并插入超鏈接以方便選擇工作表。但是如果工作簿中的工作表經(jīng)常添加和刪除，使用手工建立目錄很不方便，此時(shí)可以使用工作表的Activate事件自動(dòng)建立工作表的目錄，如下面的代碼所示。
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事件，在“目錄”工作表激活時(shí)自動(dòng)建立工作簿中除“目錄”工作表外所有工作表的目錄。
第2、3、4行代碼聲明變量類型。
第5行代碼取得A列最后非空單元格的行號(hào)。
第6行代碼設(shè)置變量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事件，當(dāng)選擇A列工作表目錄中工作表名稱時(shí)自動(dòng)選擇該單元格所對(duì)應(yīng)的工作表。
第5、6、7行代碼限制該事件觸發(fā)的條件。
第8行代碼選擇單元格所對(duì)應(yīng)的工作表。
“目錄”工作表激活后自動(dòng)在A列建立工作簿中除“目錄”工作表以外所有表的目錄，
▲028 工作表的深度隱藏
在使用VBA開發(fā)的工作簿文件完成交與用戶使用后，我們往往希望用戶在打開工作簿時(shí)啟用宏，此時(shí)除了使用“禁用宏則關(guān)閉工作簿”的功能外，還可以隱藏所有有數(shù)據(jù)的工作表，如果用戶在打開工作簿時(shí)禁用宏則只顯示一張空白的工作表，達(dá)到強(qiáng)制啟用宏的效果，代碼如下：
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事件過程，在工作簿關(guān)閉前隱藏除“空白”表以外的所有的工作表。
第3行代碼將“空白”表的Visible屬性設(shè)置為True，使其可見。
應(yīng)用于Charts和Worksheets對(duì)象的Visible屬性決定對(duì)象是否可見，語法如下：
expression.Visible
參數(shù)expression是必需的，該表達(dá)式返回上面的對(duì)象之一。
Visible屬性可以設(shè)置為表格所示的XlSheetVisibility常量之一。
第4行到第8行代碼使用For Each...Next語句遍歷工作簿中所有的工作表，將除“空白”表以外的所有工作表的Visible屬性設(shè)置為xlSheetVeryHidden，使之隱藏。
Visible屬性設(shè)置為xlSheetVeryHidden后工作表不能通過“格式”→“工作表”→“取消隱藏”菜單來顯示隱藏的工作表。
第9行代碼使用Save方法保存代碼所在工作簿的更改，在關(guān)閉工作簿時(shí)不顯示消息框。
第10行到第18行代碼是工作簿的Open事件過程，在打開工作簿時(shí)將除“空白”表以外的所有工作表的Visible屬性設(shè)置為xlSheetVisible，取消隱藏。如果打開工作簿時(shí)禁用宏，則工作簿中除了“空白”表以外，其他的工作表還處于深度隱藏的狀態(tài)，，這樣就達(dá)到強(qiáng)制用戶啟用宏的效果，當(dāng)然這還需要VBA工程保護(hù)的配合。
▲029 防止更改工作表的名稱
工作表的名稱顯示在工作表標(biāo)簽上，除了在相應(yīng)的功能菜單中可以對(duì)其進(jìn)行重命名操作外，在工作表標(biāo)簽上雙擊鼠標(biāo)也能修改工作表名稱。一旦修改了工作表名稱，可能就會(huì)產(chǎn)生一連串的問題，例如在其他工作簿中對(duì)該工作表的引用將會(huì)失效，通過工作表名稱引用工作表的代碼也將出錯(cuò)。
Excel沒有提供修改工作表名稱的相關(guān)事件，要禁止用戶修改工作表名稱，需采取其他一些▲。比如在工作表BeforeClose事件中檢驗(yàn)工作表名稱，如果工作表名稱不是指定的字符串，則將其修改為指定字符串，即保持工作表名稱不變，代碼如下。
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Sheet1.Name<> 'Excel Home' Then Sheet1.Name = 'Excel Home'
ThisWorkbook.Save
End Sub
代碼解析：
工作簿的BeforeClose事件過程，在關(guān)閉當(dāng)前工作簿時(shí)判斷Sheet1工作表名稱，如果不是指定的字符串“Excel Home”，則將其恢復(fù)為“ExcelHome”后保存工作簿，從而避免更改Sheet1工作表名稱。
▲030 工作表中一次插入多行
在工作表的中插入多行空行，需要使用Insert方法，如下面的代碼所示。
Sub InSertRows_1()
Dimi As Integer
Fori = 1 To 3
Sheet1.Rows(3).Insert
Next
End Sub
代碼解析：
nSertRows_1過程使用Insert方法在數(shù)據(jù)區(qū)域的第2行和第3行之間插入三行空行。
Insert方法應(yīng)用于Range對(duì)象時(shí)在工作表或宏表中插入一個(gè)單元格或單元格區(qū)域，其他單元格作相應(yīng)移位以騰出空間，語法如下：
expression.Insert(Shift，CopyOrigin)
參數(shù)expression是必需的，該表達(dá)式返回一個(gè)Range對(duì)象。
參數(shù)Shift是可選的，指定單元格的移動(dòng)方向。可為以下XlInsertShiftDirection常量之一：xlShiftToRight或xlShiftDown。如果省略本參數(shù)，MicrosoftExcel將依據(jù)該區(qū)域的形狀決定移動(dòng)方向。
參數(shù)CopyOrigin是可選的，復(fù)制的起點(diǎn)。
還可以使用引用多行的方法，如下面的代碼所示。
Sub InSertRows_2()
Sheet2.Range('A3').EntireRow.Resize(3).Insert
End Sub
代碼解析：
InSertRows_2過程通過引用多行區(qū)域的方法實(shí)現(xiàn)一次插入多行。
第2行代碼中的Range(“A3”).EntireRow屬性返回Range(“A3”)單元格所在的一整行，然后使用Resize屬性調(diào)整行數(shù)后插入三行空行。
也可以直接指定相應(yīng)行再調(diào)整行數(shù)后插入空行，如下面的示例代碼：
Sub InSertRows_3()
Sheet3.Rows(3).Resize(3).Insert
End Sub
聯(lián)系客服
