Excel

Floating listbox to navigate and handle visible Sheets

Ease of Use

Easy

Version tested with

2003 

Submitted by:

tstav

Description:

Press Ctrl+Alt+N (N=Navigator) while viewing any Sheet and a Listbox filled with the visible Sheets will pop up. You can select and delete sheets through the listbox. What you do through the listbox reflects in the workbook Sheets. What you do to the Sheets (delete, insert, select) reflects in the listbox. 

Discussion:

-Selecting listItem activates relative Sheet. -Deleting listItem deletes relative Sheet. -Selecting Sheet tab selects relative listItem. -Deleting Sheet tab deletes relative listItem. -Inserting Sheet updates the list with the new Sheet. -If Sheet cannot be deleted a message pops up. 

Code:

instructions for use

			

'************************************************************* 'This goes to the ThisWorkbook Module '************************************************************* Option Explicit Private Sub Workbook_Open() Application.OnKey "^%N", "ShowSheetNavigator" Application.OnKey "^%n", "ShowSheetNavigator" End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.OnKey "^%N" Application.OnKey "^%n" Unload ufmSheetNavigator End Sub Private Sub Workbook_SheetActivate(ByVal Sh As Object) Dim sht As Object 'Number of Sheets Static intSheetCount As Integer On Error GoTo ErrorHandler If Application.EnableEvents = True Then If ufmSheetNavigator.Visible Then 'If the number of sheets changed If Sheets.Count <> intSheetCount Then With ufmSheetNavigator 'Refill Sheet list .lstSheets.Clear For Each sht In Sheets If sht.Visible = xlSheetVisible Then .lstSheets.AddItem (sht.Name) End If Next 'Select listItem of ActiveSheet Application.EnableEvents = False .lstSheets.ListIndex = Sheets(ActiveSheet.Name).Index - 1 Application.EnableEvents = True End With 'Update variable with current number of sheets intSheetCount = Sheets.Count End If End If End If ErrorHandler: Application.EnableEvents = True End Sub '********************************************************** 'This goes to the General purpose code Module '********************************************************** Sub ShowSheetNavigator() Load ufmSheetNavigator ufmSheetNavigator.Show vbModeless End Sub '********************************************************** 'This goes to the Userform code Module '********************************************************** Option Explicit Private Sub UserForm_Activate() '----------------------------------------- 'Fill the list with the visible Sheets and 'select the row with the ActiveSheet name '----------------------------------------- Dim sht As Object Me.btnDelete.ControlTipText = "Delete" Me.btnDelete.Default = True Me.btnExit.Cancel = True Me.btnDelete.TakeFocusOnClick = False Me.btnExit.TakeFocusOnClick = False 'Fill the SheetList Me.lstSheets.Clear For Each sht In Sheets If sht.Visible = xlSheetVisible Then Me.lstSheets.AddItem (sht.Name) End If Next 'Select the ActiveSheet in the list Application.EnableEvents = False Me.lstSheets.ListIndex = Sheets(ActiveSheet.Name).Index - 1 Application.EnableEvents = True End Sub Private Sub lstSheets_Click() '--------------------------- 'Activate the selected Sheet '--------------------------- On Error GoTo ErrorHandler If Application.EnableEvents = True Then With Me.lstSheets If .ListIndex > -1 Then Application.EnableEvents = False Sheets(.List(.ListIndex)).Activate Application.EnableEvents = True End If End With End If ErrorHandler: Application.EnableEvents = True End Sub Private Sub btnDelete_Click() '-------------------------------------------------- 'Delete the selected Sheet and update the Sheetlist '-------------------------------------------------- Dim Answer As VbMsgBoxResult, blnNoDelete As Boolean On Error Resume Next With Me.lstSheets If Me.lstSheets.ListCount > 0 And Me.lstSheets.ListIndex > -1 Then Answer = MsgBox("Confirm delete of selected Sheet.", _ vbOKCancel + vbExclamation + vbDefaultButton2, "Delete Sheet") If Answer = vbOK Then 'Delete the selected Sheet Application.EnableEvents = False Application.DisplayAlerts = False Sheets(.List(.ListIndex)).Delete If Err Then blnNoDelete = True GoTo ErrorHandler End If Application.DisplayAlerts = True 'Delete the selected Sheet from the Sheet list .RemoveItem (.ListIndex) Application.EnableEvents = True End If Else Beep End If End With ErrorHandler: If blnNoDelete Then MsgBox "Cannot delete Sheet." & vbCrLf & _ "Probable reasons: Workbook protected or only Sheet.", _ vbExclamation, "Delete failed" End If Application.EnableEvents = True Application.DisplayAlerts = True End Sub Private Sub btnExit_Click() '--------------- 'Close Navigator '--------------- Unload Me End Sub

How to use:

  1. Alt+F11 to enter the VBE.
  2. Copy the ThisWorkbook code to the ThisWorkbook Module, the General code to the General code Module and the Userform code to the Userform Module.
  3. Close the VBE window.
  4. Press Ctrl+Alt+N or Ctrl+Alt+n and you will see the SheetNavigator.
 

Test the code:

  1. See the How to use, above.
 

Sample File:

SheetNavigator.zip 38.02KB 

Approved by mdmackillop


This entry has been viewed 350 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express