Read-Only Sheets for Non-Admins

Hi,

I have a a workbook with several sheets and a few forms.

On one of the forms, a user adds data into text boxes and clicks "add to worksheet". This then adds all of the data into the "Task Data" sheet - works perfectly.

On the main menu, there is a button that takes the users to the "Task Data" sheet. I want users to be able to see this sheet but NOT amend it - however, my form DOES need to be able to amend the worksheet. Another complication is that I DO want admins to be able to amend data.

I would do this with Sheet Protection but as I need this workbook to be shared, I can't because you can protect/unprotect sheets whilst shared.

If it makes it any easier, I have a worksheet that contains everyone's usernames and a Yes or No agaist their names dictating whether they have admin rights.

Is there a way to do this?

Thanks in advance

Hi

Just a bit of general direction:

Consider using a sheet change event that:
- Looks at the users permissions
- if (s)he does not have admin rights then use Application.Undo method

Make sure you switch off EnableEvents at the start of the code, and back on at the end.

There are many ways to determine who the user is, the easiest way that may work is to use Environ$("Username").

You can still write to the sheet with the form as long as you switch off events before attempting to write the data.

HTH
Jon

I'll try that, thanks.

I already use the Environ function, which does help considerably.

Thanks again

Why, when I use the following code do I get about a million message boxes?

Private Sub Worksheet_Change(ByVal Target As Range)
Application.Undo
If DBChangeProceed = 0 Then
Message = MsgBox("Changes to this worksheet are prohibited", vbOKOnly, "Forbidden ...")
Exit Sub
ElseIf DBChangeProceed = 1 Then
Application.RedoAction
End If
End Sub

Because each time the macro fires it triggers an Undo, which in turn fires the change event again, until excel is smart enough to realise that it is in an infinite loop. Hence why I suggested that you turn off event at the start, and turn on again at the end. ;-)

I don't see the point of calling the Undo method unless the user is not admins. That way you can also avoid the RedoAction method.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If DBChangeProceed = 0 Then
Message = MsgBox("Changes to this worksheet are prohibited", vbOKOnly, "Forbidden ...")
Application.Undo
End If
Application.EnableEvents = True
End Sub

Perfect! Thanks.

I had tried the EnableEvents bit but I didn't use application as the object which is a rookie mistake to say the least.

Cheers

Arg! Apologies.

The code works perfectly in terms of bringing up and error when DBChangeProceed = 0 but even when it = 1, the error still comes up whereas it should let the application write to the sheet.

The button the user clicks on, on the data form to add the data, starts with setting the variable to 1 and then at the end of the routine, it sets it back to 0 because no editing should be made.

Even though it is set to one, it still runs the error routine. I've tried stepping into it but I can't see why it's executing the error when it doesn't = 0

Hi

What does this return in the immediate window? Test a scenario where you expect the user to have admin rights.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Debug.Print DBChangeProceed
If DBChangeProceed = 0 Then
Message = MsgBox("Changes to this worksheet are prohibited", vbOKOnly, "Forbidden ...")
Application.Undo
End If
Application.EnableEvents = True
End Sub

Also please show me the code where the value is assigned to DBChangeProceed. Is it in the other forms module?

Do you have Option Explicit at the top of each module?

Option Explicit
Dim DBChangeProceed As Integer
Dim Message As String

Private Sub CommandButton1_Click()
DBChangeProceed = 1
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If DBChangeProceed = 0 Then
Message = MsgBox("Changes to this worksheet are prohibited", vbOKOnly, "Forbidden ...")
Application.Undo
End If
Application.EnableEvents = True
End Sub

For test purposes, I have a button on the Task Data sheet that, when pressed, sets DBChangeProceed to 1.

Once i've pressed the button, I try to amend the sheet and I the Change routine runs, but flags up the error when it should continue with the amendments like normal.

If I set DBChangeProceed to 1 in the immediate window, the code executes perfectly and skips the error and allows the change. It just doesn't work when the variable is set from anywhere else

Hi

I have attached an example for you to review. Here is how I have done it.

In the worksheet module:
Public DBChangeProceed As Boolean

Private Sub CommandButton1_Click()
DBChangeProceed = Not DBChangeProceed
End Sub

Private Sub CommandButton2_Click()
UserForm1.Show
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not DBChangeProceed Then
MsgBox Prompt:="Changes to this worksheet are prohibited!", Buttons:=vbOKOnly, Title:="Forbidden ..."
Application.Undo
End If
Finally:
Application.EnableEvents = True
End Sub


DBChangeProceed is now a boolean instead and clicking CommandButton1 (in the sheet!) will switch between True/False like a toggle (True = Admin, False = normal user).

CommandButton2 simply launches the form.
This is the form code:
Private Sub CommandButton1_Click()
Application.EnableEvents = False
Sheet1.Range("F" & Rows.Count).End(xlUp)(2).Value = Me.TextBox1.Text
Application.EnableEvents = True
End Sub


The form is callled UserForm1. TextBox1 is where you enter the text that you want written to the sheet. CommandButton1 (in the form!) is what writes the text in TextBox1 to the sheet.

Works a treat for me. ;)

It always works if you just make the buttonclick on the form a public routine which I only realised when I read your code.

Really appreciate your help, many, many thanks