Sheet Protection

  • Thread starter Thread starter Yesac
  • Start date Start date
Y

Yesac

I have a spreadsheet on a server which is used for construction project
data tracking. Different users input data into separate sheets. These
sheets rollup to summary and invoice sheets.

Is there a way to protect the sheets without using a password? Can the
username be tied to a sheet so that only certain users can edit data in
certain sheets? If not, can the password box be set to appear as soon
as input is attempted. Then can the sheet be set to automatically lock
upon exit?

Basically, going to Tools, Protection, Unprotect Sheet, then entering a
password is an annoyance that I'd like to work around because the
sheets are accessed frequently.

My current idea is to setup 2 macro buttons: "Unprotect" and "Protect",
which will cut out a few clicks.

Thanks
 
I have this in a workbook where I want certain users to see only their
worksheet:

Private Sub Workbook_Open()

'MsgBox "This worksbook has 6 worksheets: one named Control which is always
visible, and sheets named Name1, Name2, Name3, Name4, and Name5. If you
type one of those names you will see a worksheet with the corresponding name
on the tab. If you do not enter one of the names an error will be
generated."

Call HideSales
Call GetValidInput
End Sub

Sub HideSibs()
Worksheets("Name1").Visible = False
Worksheets("Name2").Visible = False
Worksheets("Name3").Visible = False
Worksheets("Name4").Visible = False
Worksheets("Name5").Visible = False
End Sub

Function GetValidInput() As String
Dim i As String
i = InputBox("Please enter your name, capitalizing the first letter:")
Select Case i
Case Is = ""
msgBox "You have not entered a valid name. You will have to press OK to
let this file close and try again."
ThisWorkbook.Save
ThisWorkbook.Close
Case Is = "Name1"
Worksheets("Name1").Visible = True
Worksheets("Name1").Select
ActiveSheet.Range("A1").Select
Case Is = "Name2"
Worksheets("Name2").Visible = True
Worksheets("Name2").Select
ActiveSheet.Range("A1").Select
Case Is = "Name3"
Worksheets("Name3").Visible = True
Worksheets("Name3").Select
ActiveSheet.Range("A1").Select
Case Is = "Name4"
Worksheets("Name4").Visible = True
Worksheets("Name4").Select
ActiveSheet.Range("A1").Select
Case Is = "Name5"
Worksheets("Name5").Visible = True
Worksheets("Name5").Select
ActiveSheet.Range("A1").Select
Case Else
msgBox "You have not entered a valid name. You will have to press OK to
let this file close and try again."
ThisWorkbook.Save
ThisWorkbook.Close
End Select
End Function
 
Back
Top