CODEWITHSUNDEEP
excelvba
CAES_MATT
CAES_MATT

Reputation: 19

How do I allow only myself to view a spreadsheet but everyone else can only a userform?

Trying to work out how only I can see the work book and everyone else can see the userform, based on network username.

Here's what I've got so far:

Private Sub Workbook_Open()

    Application.Visible = False
    UtilitiesReportingTool.Show

End Sub

Upvotes: 0

Views: 46

Answers (1)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19847

You'll need a couple of things to stop your users looking at your worksheets - and these can easily be broken with a little knowledge.

  • Create a sheet with big text saying "Please enable macros"
  • Very hide all sheets except the macro enable sheet.
  • Create these two procedures in a normal module 
Sub StartUp()

    Dim wrkSht As Worksheet

    If Environ("username") <> "CAES_MATT" Then
        Application.Visible = False
        UtitlitiesReportingTool.Show
    Else
        For Each wrkSht In ThisWorkbook.Worksheets
            wrkSht.Visible = xlSheetVisible
        Next wrkSht
    End If
End Sub

Sub ShutDown()

    Dim wrkSht As Worksheet

    For Each wrkSht In ThisWorkbook.Worksheets
        Select Case wrkSht.CodeName
            Case "Sheet1" 'This should really be shtMacroEnable or something.
                wrkSht.Visible = xlSheetVisible
            Case Else
                wrkSht.Visible = xlSheetVeryHidden
        End Select
    Next wrkSht

End Sub

In your Workbook_Open event add this code: 

Private Sub Workbook_Open()
    StartUp
End Sub

In your UserForm_Terminate event add this: 

Private Sub UserForm_Terminate()
    Application.Visible = True
    ShutDown
End Sub

When you open the workbook only the 'Enable Macros' sheet will be visible if macros aren't enabled. When macros are enabled the form is displayed, unless it's you in which case all the sheets are unhidden.

When the form is closed everything except the sheet with codename Sheet1 is hidden.

I may have missed something ... heading home.

Upvotes: 1

Related Questions