Force Read Only

  • Thread starter Thread starter Paul Watkins
  • Start date Start date
P

Paul Watkins

Hi
I have a spreadsheet which requires a log-on via a userform to access the
main book.
i have set up various users with passwords that can access the book.(all
with Read/Write Access)

I want to allocate a user (guest) to the book, but i don't want this user to
have write access to the book.

Once i have entered the user & password into the userform, the user is
displayed on sheet 1

Is it possible to have the cell that logs the user (D5) on Sheet 1 to
recognise the user 'guest' and then make the book 'Read Only'?

Thanks in advance


Paul
 
Maybe something like this will get you started:

Option Explicit
Sub auto_open()

Dim UserIsReadOnly As Boolean
Dim DestCell As Range

'validate user some how
UserIsReadOnly = True 'false for testing

With Worksheets("sheet1")
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
'log in user
With DestCell
.Value = Application.UserName 'or whatever
With .Offset(0, 1)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
.Offset(0, 2).Value = UserIsReadOnly
End With
End With

ThisWorkbook.Save

If UserIsReadOnly Then
ThisWorkbook.ChangeFileAccess Mode:=xlReadOnly
Else
'ok to keep going...
End If

End Sub
 
Thanks Dave

It works, but i would like to try and stop this message popping up.

'Do you want to save changes before switching file status'

I've tried adding 'Application.DisplayAlerts = False' , but it still
appears.
Any Ideas

Thanks in advance

Paul
 
Did you drop a line from the code?

ThisWorkbook.Save

(Or even make a change to the workbook after the save?)

I've found that if the workbook is saved, then the prompt doesn't occur.

But if excel thinks the workbook needs a save, then it'll show up.

So the answer might be different depending on what you did.

Something like this may cause more trouble:

Option Explicit

Sub auto_open()

Dim UserIsReadOnly As Boolean
Dim DestCell As Range
Dim RealSavedStatus As Boolean

'validate user some how
UserIsReadOnly = True 'false for testing

With Worksheets("sheet1")
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
'log in user
With DestCell
.Value = Application.UserName 'or whatever
With .Offset(0, 1)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
.Offset(0, 2).Value = UserIsReadOnly
End With
End With

ThisWorkbook.Save

'something that changed the workbook
Worksheets("sheet1").Range("a1").Value = "yo, a change!"

RealSavedStatus = ThisWorkbook.Saved
ThisWorkbook.Saved = True
If UserIsReadOnly Then
ThisWorkbook.ChangeFileAccess Mode:=xlReadOnly
Else
'ok to keep going...
End If
ThisWorkbook.Saved = RealSavedStatus

End Sub

If you changed something, then ignoring that change and making the workbook
readonly, then that change won't get saved. I'm not sure if it's a change you'd
want to keep.
 
Back
Top