Prevent Viewing Data If Macros Not Enabled

Is there a way to do the following? Prevent viewing of data if Macros are not enabled. And if Macros Enabled, run the Macro below.

Scenario: I have a file with Multiple users. Each user will get a PivotTable refresh depending on User Name. So, when User opens the file and Macros are not Enabled...then, either Pop-Up box asking User to Enable Macros or Ask User to Enabled Macros before opening the file. If Macros enabled, then run Macro below. The goal is for User NOT to see the entire file. Records are assigned depending on User Name.

Here is my Macro, Macro below does not include above request.

Public Function UserName()
    UserName = Environ$("UserName")
End Function

'Private Sub Workbook_Open()
'Macro2
'End Sub

Sub Macro2()
On Error GoTo MyErrorHandler:
Dim E_name As String
E_name = UserName()
If Len(E_name) > 0 Then
  Sal = Application.WorksheetFunction.VLookup(E_name, Sheet1.Range("EK:EK"), 1, False)
     Range("B12").Select
    ActiveSheet.PivotTables("PivotTable2").PivotFields("User Name").CurrentPage = _
        UserName()
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("User Name")
        .PivotItems(UserName).Visible = True
    End With
    Range("A15").Select
    Selection.End(xlToRight).Select
    Selection.End(xlDown).Select
    Selection.ShowDetail = True
    Sheets("Named Accounts").Select
    ActiveSheet.PivotTables("PivotTable2").ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Table2", Version _
        :=xlPivotTableVersion14)
    ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
    Sheets("sheet1").Select
    Application.DisplayAlerts = False
    ActiveWindow.SelectedSheets.Delete
    Application.DisplayAlerts = True
    Sheets("Sheet2").Select
    ActiveWindow.SelectedSheets.Visible = False
    Range("B12").Select
  Else
        MsgBox ("Sorry! User Name Not Present in The Table. This File Will Now Close.")
  ActiveWorkbook.Close
End If
Exit Sub
MyErrorHandler:
If Err.Number = 1004 Then
  MsgBox "Sorry! User Name Not Present in The Table. This File Will Now Close."
    ActiveWorkbook.Close
End If
End Sub
gizaguirreAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
What you can do is hide that worksheet in the file and then make it visible only by macro code that way users have to enable the macros only for sure to make it visible...

In additional you can just show sheet1 which is saying in order to see the worksheets please enable the macros..

Something like this...

Private Sub Workbook_Open()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Sheet1" Then
            ws.Visible = True
        End If
    Next ws

    Sheets("Sheet1").Visible = False
End Sub

Open in new window


Saurabh...
0
gizaguirreAuthor Commented:
Saurabh, thanks for the Macro. However, if the Sheet1 (or DATA SHEET) is hidden...User is still able to Unhide. Which means, they have full access to the file. I want to prevent for User to look at other's clients.
Thanks, Gustavo
0
Saurabh Singh TeotiaCommented:
Gutsavo,

You can hide those sheets by macro by very hidden which is like this...

Lets say i want to hide sheet2 very hidden..

Sheets("sheet2").Visible = xlVeryHidden

Open in new window


Now a very hidden worksheet can be made visible only by the visual editor a normal user can't make it visible by going into hide and unhide option..

Once you use this option then you can protect your vb code by going in properties and selecting the option of protection and locked for viewing and giving a password their...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.