Greg Truby
MrExcel MVP
- Joined
- Jun 19, 2002
- Messages
- 10,030
Alrighty folks. Most of y'all are familiar with the "force the user to enable macros" trick that is explained nicely at dk's site. That's all fine and dandy until we get into the issue of folks trying to:
, well I'd be much obliged for yer input.
{Edit}In case you're wondering about some of the 2-second delays, the workbook I was using this on was rather large. If I tested on a miniscule WB, the delays were not needed. But when I "moved to production" with a large WB, VBA would "get ahead of itself" and unhide the sheets too quickly and the saved versions had the wrong sheets visible/hidden. I would love to have some mechanism in UnHideSheets that could test whether Excel is done saving the workbook and only then proceed to unhide them, but that exceeds my knowledge at this moment.
- save in mid-stream, i.e. not at closing time
- trying to close the workbook without being forced into saving. i.e. like myself they play all sorts of "what if's" with stuff and now they just want to close the workbook and re-open it with everything restored back to before they started playing about.
, well I'd be much obliged for yer input.
Rich (BB code):
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
' _________.xls: basxl_ForceMacrosAtOpen
' ______________________________________
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
' Date: April 2005
' Author: Greg Truby
' Summary: Generic module that can be imported into any workbook where macros
' need to be enabled for the workbook to function properly and a
' "Start Up" or "Splash" worksheet greets the user if macros are
' disabled, giving instructions on how to enable macros &/or
' trust content with my digital signature.
'
' Variation on basic premise described in many place on internet
' one example being:
' http://www.danielklann.com/excel/force_macros_to_be_enabled.htm
' _____________________________________________________________________________
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
' Contents:
' ¯¯¯¯¯¯¯¯¯
' Sub HideSheets
' Sub UnHideSheets
'
' ...copy of code to added to workbook code page...
' Sub wb_BeforeClose
' Sub wb_BeforeSave
' Sub wb_Open
' Sub wb_SheetSelectionChange
' _____________________________________________________________________________
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
' General Comments:
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
' a. Variables prefixed with a "g_" are globals.
' b. Variables prefixed with a "m_" are scoped to the module.
' c. Variables prefixed with a "c_" are constants.
' d. Leave only one underscore if concatenating prefixes
' i.e. "gc_" & "mc_"
' e. Conditional subroutine exits should carry a "|--¿xsub?-->"
' on the right edge of the line.
' f. Conditional loop exits should carry a "|--¿xloop?-->"
' on the right edge of the line.
' g. Conditional goto's should carry a "|--¿goto?-->"
' on the right edge of the line.
' _____________________________________________________________________________
'
Option Explicit
Private m_rngActive As Range, m_objActiveSheet As Object
Public Sub HideSheets()
'¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
' Use generic Object instead of Worksheet since
' the some sheets might be chart sheets.
Dim objSheet As Object
Set m_objActiveSheet = ActiveSheet
If m_objActiveSheet.Type = XlSheetType.xlWorksheet Then
Set m_rngActive = ActiveCell
Else
Set m_rngActive = Nothing
End If
wsStartUp.Visible = xlSheetVisible
For Each objSheet In ThisWorkbook.Sheets
If Not objSheet Is wsStartUp Then objSheet.Visible = xlSheetVeryHidden
Next objSheet
Application.Wait Now + TimeSerial(0, 0, 2)
End Sub
Public Sub UnHideSheets()
'¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
Dim objSheet As Object
For Each objSheet In ThisWorkbook.Sheets
objSheet.Visible = xlSheetVisible
Next objSheet
wsStartUp.Visible = xlSheetVeryHidden
If m_objActiveSheet Is Nothing Then
wsMain.Activate ' ««« Use CodeName for Appropriate Default Sheet «««
Range("A1").Select
Else
m_objActiveSheet.Activate
If Not m_rngActive Is Nothing Then m_rngActive.Select
End If
ThisWorkbook.Saved = True
End Sub
'___________________________________________________________________________________
'¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
' Code for the WORKBOOK code page
'___________________________________________________________________________________
'Option Explicit
'Private m_booClosingTime As Boolean
'Private Sub Workbook_BeforeClose(Cancel As Boolean)
'¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
' m_booClosingTime = True
'End Sub
'Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
' HideSheets
' ' Don't set ONTIME if closing or if the name will be changed. Doing
' ' so would force open a copy of the old file.
' If SaveAsUI Then
' MsgBox "Select any cell or hit an arrow key to restore view.", _
' vbInformation, "Saving as New"
' ElseIf Not m_booClosingTime Then
' Application.OnTime Now + TimeSerial(0, 0, 2), "UnHideSheets"
' End If
'End Sub
'Private Sub Workbook_Open()
'¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
' UnHideSheets
' Me.Saved = True
'End Sub
'Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
'¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
' m_booClosingTime = False
' If wsMain.Visible <> xlSheetVisible Then UnHideSheets ' ««« edit to appropriate CodeName
'End Sub
