公司的日本人喜歡用excel寫文檔, 并且喜歡用很多的sheet,

然而在excel中, 隨著工作表數量的增加, 工作表的瀏覽和定位就會變得麻煩起來,

于是我便希望能找到一個方法, 可以更容易, 更直觀(一眼看到更多的sheet名)的導航sheet.

?

為此, 我google了一下,解決方案有下面兩個

?

方法1 Dave Peterson's Sheet Navigator Toolbar for Workbook Sheets

詳細網址如下:

http://www.contextures.com/xlToolbar01.html

效果圖如下:

?

這個教程講解的比較詳細, 不僅付有Sheet Navigator的代碼,

同時還附有如何自定義一個toolbar, 如果自定義這個Sheet Navigator toolbar.

鏈接上還附有這個excel插件的下載,

為了防止鏈接失效, 我把這個addin下載轉載到了附件中,

將其置于下面路徑中便可以直接使用:

C:\Documents and Settings\<windows username>\Application Data\Microsoft\AddIns

?

同時, 這個插件還有一個excel 2007的版本, 他們的界面有所不同.

Sheet Navigator - List and Sort Excel Sheets - Excel 2007

?

?

方法2. Bob Phillips' BrowseSheets

其中的兩個連接如下:

http://help.lockergnome.com/office/Macro-Sheet--ftopict715336.html

http://www.pcreview.co.uk/forums/selecting-workbook-worksheet-browse-button-t966990.html

我自定義了excel的快捷鍵, 去去執行這段腳本, 同時也為他, 在我自定義的toobar上面追加了一個按鈕

他的效果圖如下:

代碼如下:

?

Sub BrowseSheets() Const nPerColumn As Long = 38 'number of items per column Const nWidth As Long = 13 'width of each letter Const nHeight As Long = 18 'height of each row Const sID As String = "___SheetGoto" 'name of dialog sheet Const kCaption As String = " Select sheet to goto"'dialog captionDim i As Long Dim TopPos As Long Dim iBooks As Long Dim cCols As Long Dim cLetters As Long Dim cMaxLetters As Long Dim cLeft As Long Dim thisDlg As DialogSheet Dim CurrentSheet As Worksheet Dim cb As OptionButtonApplication.ScreenUpdating = FalseIf ActiveWorkbook.ProtectStructure ThenMsgBox "Workbook is protected.", vbCriticalExit SubEnd IfOn Error Resume NextApplication.DisplayAlerts = FalseActiveWorkbook.DialogSheets(sID).DeleteApplication.DisplayAlerts = TrueOn Error GoTo 0Set CurrentSheet = ActiveSheetSet thisDlg = ActiveWorkbook.DialogSheets.AddWith thisDlg.Name = sID.Visible = xlSheetHidden'sets variables for positioning on dialogiBooks = 0cCols = 0cMaxLetters = 0cLeft = 78TopPos = 40For i = 1 To ActiveWorkbook.Worksheets.CountIf i Mod nPerColumn = 1 ThencCols = cCols + 1TopPos = 40cLeft = cLeft + (cMaxLetters * nWidth)cMaxLetters = 0End IfSet CurrentSheet = ActiveWorkbook.Worksheets(i)cLetters = Len(CurrentSheet.Name)If cLetters > cMaxLetters ThencMaxLetters = cLettersEnd IfiBooks = iBooks + 1.OptionButtons.Add cLeft, TopPos, cLetters * nWidth, 16.5.OptionButtons(iBooks).text = _ActiveWorkbook.Worksheets(iBooks).NameTopPos = TopPos + 13Next i.Buttons.Left = cLeft + (cMaxLetters * nWidth) + 24CurrentSheet.ActivateWith .DialogFrame.Height = Application.Max(68, _Application.Min(iBooks, nPerColumn) * nHeight + 10).Width = cLeft + (cMaxLetters * nWidth) + 24.Caption = kCaptionEnd With.Buttons("Button 2").BringToFront.Buttons("Button 3").BringToFrontApplication.ScreenUpdating = TrueIf .Show ThenFor Each cb In thisDlg.OptionButtonsIf cb.Value = xlOn ThenActiveWorkbook.Worksheets(cb.Caption).SelectExit ForEnd IfNext cbElseMsgBox "Nothing selected"End IfApplication.DisplayAlerts = False.DeleteEnd WithEnd Sub

?

?

?

其他鏈接

?

Getting Started with Macros and User Defined Functions

http://dmcritchie.mvps.org/excel/getstarted.htm

Ron's Excel Tips

http://www.rondebruin.nl/tips.htm

Application Events

http://www.cpearson.com/excel/AppEvent.aspx

Events And Event Procedures In VBA

http://www.cpearson.com/excel/Events.aspx

?

?

?

?

總結

以上是生活随笔為你收集整理的Excel VBA: 工作表(Sheet)浏览导航插件的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。