Restrict access to portions of the Sheet to specific users?

[CritterLes]

I Have a sheet that has data for 5 different classes. Each class should be viewed by only 1 teacher. Is there a way to define sections of the sheet and then give that access to individual users?

I tried doing this with different worksheets and it's great up to the point where I have to append all the individuals sheets into one. The append process has been a challenge to automate into a button activated macro.
Any ideas out there?

 

[CalcSux78]

You might try setting up a sheet level macro triggered on sheet activation to look at the current user and hide/show ranges based off of what user is viewing.

Private Sub Worksheet_Activate()
Select Case Application.UserName
    Case "John Doe"
        Range("A1:A10").EntireRow.Hidden = False
        Range("A11:A30").EntireRow.Hidden = True
    Case "Jane Smith"
        Range("A1:A30").EntireRow.Hidden = True
        Range("A11:A20").EntireRow.Hidden = False
    Case Else
        Range("A1:A30").EntireRow.Hidden = True
End Select
End Sub

 

[CritterLes]

How would you define various users. Is this a user macro with a password?

 

[CalcSux78]

My appologies. Yes, this is a macro (VB code) to look at the username of the person logged into the computer running your workbook. If you've never worked with Visual Basic, it may be a bit complex at first.

Are you comfortable with using macros and vb coding? If so I can explain a bit more.