Enabled macros

[raw]

Hi

I have workbook that needs macros to be enabled i have found a thread
see link below that displays a splash screen if macros are disabled BUT
the code hides all the sheets and shows only the splash screen the
problem i have is i have about 30 odd sheets that are hidden all the
time and about 10 that are visable, how would are alter the code to
suit my needs


http://www.excelforum.com/showthread.php?t=470370&highlight=enableing+macros

Thanks

 

[Bob Phillips]

You could try this approach. Your sheets that you want permanently hidden,
set them as xlSheetVeryHidden. Then add this code, which incidentally, also
reverts things at the end

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Splash").Visible = xlSheetVisible
For Each sh In Sheets
If sh.Name <> "Splash" Then
If sh.Visible = xlSheetVisible Then
sh.Visible = xlSheetHidden
End If
End If
Next sh
thisworkbbok.Save
End Sub

Private Sub Workbook_Open()
Dim sh As Object
For Each sh In Sheets
If sh.Visible <> xlSheetVeryHidden Then
sh.Visible = True
End If
Next sh
Sheets("Splash").Visible = xlSheetVeryHidden
End Sub


'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)