[RESOLVED] Forcing users to enable macros

[jdubsOR]

Hello,
I found this nifty method to force users to enable macros and so far it's working great and was very easy to implement.
See method here: http://www.vbaexpress.com/kb/getarticle.php?kb_id=379

The problem now is that I need to modify the code so that instead of all the sheets un-hiding only four of the sheets to unhide and since this code was a piece of cake to implement and I am a noob when it comes to this stuff I have no idea where to start.
Those four sheets are titled:

String Sizing
Voltage Drop
Performance
Custom Equipment

The code which is located in the ThisWorkBook object:

Option Explicit

Const WelcomePage = "Macros"

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    'Turn off events to prevent unwanted loops
    Application.EnableEvents = False
    
    'Evaluate if workbook is saved and emulate default propmts
    With ThisWorkbook
        If Not .Saved Then
            Select Case MsgBox("Do you want to save the changes you made to '" & .Name & "'?", _
                vbYesNoCancel + vbExclamation)
                Case Is = vbYes
                    'Call customized save routine
                    Call CustomSave
                Case Is = vbNo
                    'Do not save
                Case Is = vbCancel
                    'Set up procedure to cancel close
                    Cancel = True
            End Select
        End If
    
    'If Cancel was clicked, turn events back on and cancel close,
    'otherwise close the workbook without saving further changes
        If Not Cancel = True Then
            .Saved = True
            Application.EnableEvents = True
            .Close savechanges:=False
        Else
            Application.EnableEvents = True
        End If
    End With
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    'Turn off events to prevent unwanted loops
    Application.EnableEvents = False
    
    'Call customized save routine and set workbook's saved property to true
    '(To cancel regular saving)
    Call CustomSave(SaveAsUI)
    Cancel = True
    
    'Turn events back on an set saved property to true
    Application.EnableEvents = True
    ThisWorkbook.Saved = True
End Sub

Private Sub Workbook_Open()
    'Unhide all worksheets
    Application.ScreenUpdating = False
    Call ShowAllSheets
    Application.ScreenUpdating = True
End Sub

Private Sub CustomSave(Optional SaveAs As Boolean)
    Dim ws As Worksheet, aWs As Worksheet, newFname As String
    'Turn off screen flashing
    Application.ScreenUpdating = False
    
    'Record active worksheet
    Set aWs = ActiveSheet
    
    'Hide all sheets
    Call HideAllSheets
    
    'Save workbook directly or prompt for saveas filename
    If SaveAs = True Then
        newFname = Application.GetSaveAsFilename( _
            fileFilter:="Excel Files (*.xls), *.xls")
        If Not newFname = "False" Then ThisWorkbook.SaveAs newFname
    Else
        ThisWorkbook.Save
    End If
    
    'Restore file to where user was
    Call ShowAllSheets
    aWs.Activate
    
    'Restore screen updates
    Application.ScreenUpdating = True
End Sub

Private Sub HideAllSheets()
    'Hide all worksheets except the macro welcome page
    Dim ws As Worksheet
    
    Worksheets(WelcomePage).Visible = xlSheetVisible
    
    For Each ws In ThisWorkbook.Worksheets
        If Not ws.Name = WelcomePage Then ws.Visible = xlSheetVeryHidden
    Next ws
    
    Worksheets(WelcomePage).Activate
End Sub

Private Sub ShowAllSheets()
    'Show all worksheets except the macro welcome page
    
    Dim ws As Worksheet
    
    For Each ws In ThisWorkbook.Worksheets
        If Not ws.Name = WelcomePage Then ws.Visible = xlSheetVisible
    Next ws
    
    Worksheets(WelcomePage).Visible = xlSheetVeryHidden
End Sub

Any help greatly appreciated! Cheers.

John

[dunfiddlin]

I am a noob when it comes to this stuff I have no idea where to start.

Yet you're already undermining people's security measures. Just think what you'll be able to achieve when you work out what you're doing!

[jdubsOR]

What will I achieve? Users can choose not to enable macros and users can choose not agree to our terms. This is a tool that my company provides for free, nobody is forced into using it. Whats the issue?

[jayinthe813]

What will I achieve? Users can choose not to enable macros and users can choose not agree to our terms. This is a tool that my company provides for free, nobody is forced into using it. Whats the issue?

I think his point was that you should not be 'forcing' users to enable macros, that being you are undermining the purpose of security in excel altogether. And you really should not be forcing users to enable macros, you should let them enable it if your document is to be trusted. Any code you create to "force" the macro can be stripped out or bypassed anyway. there is no real way to secure a vba project, even the password protected projects can be rather easily cracked.

that being said, this is posted in the wrong forum, but nonetheless:

Private Sub ShowAllSheets()
    'Show all worksheets except the macro welcome page
    
    Dim ws As Worksheet
    
    For Each ws In ThisWorkbook.Worksheets
        If Not ws.Name = WelcomePage Then ws.Visible = xlSheetVisible
    Next ws
    
    Worksheets(WelcomePage).Visible = xlSheetVeryHidden
End Sub

you need to modify the loop to not show them all

    For Each ws In ThisWorkbook.Worksheets
        Select Case ws.Index
        Case Not 1 To 4
        ws.Visible = True
        Case Else
        ws.Visible = False
        End Select
        
    Next ws

where 1 to 4 is the index of the first sheet, second sheet, third sheet, fourth sheet.

[jdubsOR]

Just to be clear, when I say I force users to enable macros, all i mean is that they are told that this workbook requires users to enable macros and they are given directions on how to enable them.

How does a user know that a file is to be trusted without enabling macros in the first place? Couldn't you create a harmless looking workbook with "extra features" if they enable macros and once they enable them the malicious content happens instantly?

Thanks for the help by the way!

[jayinthe813]

It is dependent upon the individual user's security settings. The default security will not let you execute code in Excel unless explicitly given permission on opening, even saving as a macro-enabled workbook.

[a_27826]

If you email the workbook from within Excel once it has been opened and macros enabled, does the code still work for the recipient of the mail?