1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel shared read only problem

Discussion in 'Business Applications' started by xbox_ian, Jan 7, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. xbox_ian

    xbox_ian Thread Starter

    Joined:
    Apr 27, 2007
    Messages:
    21
    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.
     
  2. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    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.
     
  3. xbox_ian

    xbox_ian Thread Starter

    Joined:
    Apr 27, 2007
    Messages:
    21
    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
     
  4. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    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...

    Code:
    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. :)
     
  5. xbox_ian

    xbox_ian Thread Starter

    Joined:
    Apr 27, 2007
    Messages:
    21
    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
     
  6. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    COD4? No way! Me too! :D 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
     
  7. xbox_ian

    xbox_ian Thread Starter

    Joined:
    Apr 27, 2007
    Messages:
    21
    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! :)
     
  8. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    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 ...

    Code:
    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
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/669075

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice