Forum Discussion

MstrV's avatar
MstrV
Copper Contributor
Mar 13, 2020

Excel worksheet permissions

I need to give full access without a password to one person on an Excel Document. I need to restrict others to be able to edit only two selected columns. I have shared this document (legacy) over our network of which all computers/members are in a domain. I am able to give access to everybody for the two selected columns but I cannot give complete access to one person without having to use a password. I have attempted to give access using ranges and permissions to certain individuals but am having no success.

I also activated autosave on this shared document over the network but that is not working either.

Doing a manual save the document updates but has to be manually saved on each computer in order to see the changes.

Any help would be appreciated.

excel

4 Replies

  • mtarler's avatar
    mtarler
    Silver Contributor
    Mar 13, 2020

    MstrV  you could lock the document to how most users will use it and in VBA, onload you can check for that particular user and unlock all the sheets.  the sheet PWs would have to be kept in VBA which means you should also password protect the VBA. [edit] you will also need to add a beforeclose function to re-lock all the sheets.

    • MstrV's avatar
      MstrV
      Copper Contributor
      Mar 13, 2020

      mtarler 

      I will need to do some pretty extensive research into VBA before I tackle this.

      Not at all familiar with coding.

      Thanks for the info.

      • mtarler's avatar
        mtarler
        Silver Contributor
        Mar 13, 2020

        MstrV  actually it isn't hard:

        open the workbook and click alt-F11 (opens VBA window)

        on left expand your VBAProject (yourworkbookname.xlsm) (NOTE: you will need to make sure your workbook is saved as a format that allows macros like .xlsm or .xlsb)

        expand Microsoft Excel objects

        double-click on ThisWorkbook (should open code window to the right)

        in the code window you basically want:

         

        Public Const PW = "your password"

        Private Sub Workbook_Open()

          if Application.UserName="user's name" then

                ActiveWorkbook.Unprotect (PW)
                ActiveWorkbook.Sheets("sheet name").Unprotect (PW)

          end if

        End Sub

        Private Sub Workbook_BeforeClose(Cancel As Boolean)
                ActiveWorkbook.Protect (PW)
                ActiveWorkbook.Sheets("sheet name").Protect (PW)
        End Sub

         

        NOTE: I used the same password for both protecting the workbook and the sheet 

        NOTE2: I assumed 1 sheet.  If you have more sheets you can use a loop or a For Each statement.

Resources