• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 47
  • Last Modified:

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()
'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)
    ActiveSheet.PivotTables("PivotTable2").PivotFields("User Name").CurrentPage = _
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("User Name")
        .PivotItems(UserName).Visible = True
    End With
    Selection.ShowDetail = True
    Sheets("Named Accounts").Select
    ActiveSheet.PivotTables("PivotTable2").ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Table2", Version _
    Application.DisplayAlerts = False
    Application.DisplayAlerts = True
    ActiveWindow.SelectedSheets.Visible = False
        MsgBox ("Sorry! User Name Not Present in The Table. This File Will Now Close.")
End If
Exit Sub
If Err.Number = 1004 Then
  MsgBox "Sorry! User Name Not Present in The Table. This File Will Now Close."
End If
End Sub
  • 2
1 Solution
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

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
Saurabh Singh TeotiaCommented:

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...
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now