# Excel shared read only problem



## xbox_ian (Apr 27, 2007)

I have an excel sheet that is set up as a shared workbook. The file is used for scheduling engineers and needs to be constantly updated by multiple users.
I have set up NTFS permissions so that all the relevant members of staff can make changes and update the file as required. I have setup a separate user who has read only access. This user is for a display screen where the engineers can come and look where they are scheduled to be working. I do not want the sheet to be modified by this user.

The problem I then have is that the spreadsheet does not update automatically every 5 mins (as it is set to). If the sheet is exited and re-opened, the changes show up.

Does anyone know of a way around this?
(the engineers need a mouse to be able to scroll around the sheet)

thanks
Ian.


----------



## Zack Barresse (Jul 25, 2004)

Hi there Ian,

I believe that read-only negates other features of updating shared workbooks, because the catalyst is generally set up as the Save action. You could do this with some VBA code though. Let us know if you'd like to go that route.


----------



## xbox_ian (Apr 27, 2007)

Thanks firfytr

I have been exploring the options with shared protected worksheets. From reading some support articles I thought I had cracked it by protecting part of a sheet and securing it using NTFS permissions so that the single user does not have write access.
However, this seemed to stop working once the workbook was shared.

The VBA route may be the only way I can come to a sloution on this one. (unless I am doing something wrong with the sharing and protecting method). Surely this scenario must have been encountered by someone else at some point.

Any further help would be appreciated

Thanks
Ian


----------



## Zack Barresse (Jul 25, 2004)

Since it is this one person and the problem is local, you should use your Personal.xls file and use some code such as the following...


```
Sub ReOpenFile()
    Dim Msg As VbMsgBoxResult, strPrompt As String, strFile As String
    Const NL As String = vbNewLine
    If ActiveWorkbook Is Nothing Then
        MsgBox "There is no open workbook!", vbCritical, "ERROR!"
        Exit Sub
    End If
    If Len(ActiveWorkbook.Path) = 0 Then
        MsgBox "Activeworkbook is not saved yet!", vbCritical, "ERROR!"
        Exit Sub
    End If
    strPrompt = "Are you sure you want to close/re-open '" & ActiveWorkbook.Name & "'?" & NL & NL
    strPrompt = strPrompt & "You will lose any unsaved data."
    Msg = MsgBox(strPrompt, vbYesNo, "CLOSE/RE-OPEN FILE?")
    If Msg = vbNo Then Exit Sub
    strFile = ActiveWorkbook.FullName
    ActiveWorkbook.Close savechanges:=False
    Workbooks.Open strFile
End Sub
```
On a non-related side note, do you game? Online? Curious. I do. And I love my Xbox.


----------



## xbox_ian (Apr 27, 2007)

Thanks again...

I'm guessing that code is used to close and re-open the workbook automatically? therefore forcing an update to the shared sheet? (I'm pretty useless with excel apart from the basics).
Where do I use this code? and does it affet all users. The workbook is stored on a network share and about 5 people are likely to have it open at once on different computers.

In response to your other question... Xbox live! I love it. Especially Call of Duty 4 at the moment. Only started playing it a couple of weeks ago but its addictive. You will have to send me your gamertag if you are on live

Cheers
Ian


----------



## Zack Barresse (Jul 25, 2004)

COD4? No way! Me too!  My GT is now 'KAOS FF Zack'. Would be so funny if we've played b4. LOL!

About the code, yeah, it does just what you think it does. I can comment it up for you if you'd like, but basically it will do a couple of cross-checks, then if everything is good it will take the name/path of the open file, close it, then re-open it. The whole basis for this is that the code would be stored in some other file, open all the time, where you could run the code from and not affect anything or anyone else. The workbook I speak of is of course the Personal.xls file. If you do not have one, just record a macro, when it asks you where to store it, choose Personal Excel Workbook from the drop down. You can stop the macro as soon as it starts (clicking the Stop button) and it will create the file for you, and it automatically puts it into your XLSTART folder.

Once you have a Personal.xls file, to use my code, follow these steps:

Press Alt + F11
Press Ctrl + R
Expand Personal.xls file on left
In any standard module (Insert | Module) past the code


----------



## xbox_ian (Apr 27, 2007)

Thanks again...

I have created the personal.xlsb file (Excel 2007) and copied the code into a new module.
Do I have to do anything in order to set when and how often the workbook closes and re-opens. Ideally this needs to be done every 5 minutes.

Sorry if these questions seem pretty basic, but I'm completely new to using the visual basic editor in excel.

cheers
Ian.

PS. I will add your gamertag on Xbox, and maybe show you how to play COD4!


----------



## Zack Barresse (Jul 25, 2004)

Okay, well this is difficult to negotiate and can be very, very finicky. Personally I do not like to do things like this, because I just do not like having something running behind the scenes with as little control as we have over it. But here is the shell code you can use ...


```
Option Explicit

Public RunWhen As Double
Public Const cIntervalSeconds As Long = 2
Public Const cRunSub As String = "ReOpenFile"

Sub StartTimer()
    RunWhen = Now + TimeSerial(0, 0, cIntervalSeconds)
    Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunSub, Schedule:=True
End Sub

Sub StopTimer()
    On Error Resume Next
    Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunSub, Schedule:=False
End Sub

Sub ReOpenFile()
    Dim msg As VbMsgBoxResult
    Const NL As String = vbNewLine
    '///////////////////////////////////////////////////////////////////
    '///////////////////////////////////////////////////////////////////
    'YOUR CODE GOES RIGHT HERE.  UPON COMPLETION, YOU CAN USE A
    'MESSAGE BOX TO SEE IF YOU WANT TO KEEP GOING OR NOT, OR USE
    'SOME OTHER EVENT. BUT YOU MUST RUN THE OTHER ROUTINE TO STOP
    'THE TIMED ROUTINE FROM RUNNING CONSTANTLY.
    '///////////////////////////////////////////////////////////////////
    msg = MsgBox("ran", vbYesNo)
    '///////////////////////////////////////////////////////////////////
    '///////////////////////////////////////////////////////////////////
    If msg = vbNo Then
        Call StopTimer
    Else
        Call StartTimer
    End If
End Sub
```
One of the best spots for researching the OnTime method is Chip Pearson's site: http://www.cpearson.com/excel/OnTime.aspx

Edit: Oh, and I added you on my FL. (At least I hope that was you.) You probably _could _school me at COD4! Just started Veteran campaign. 

HTH


----------

