Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Viewing remote workbooks (97 sr2 on XP)

    Need some logical assistance please.
    Workbook1 - contains one worksheet. Worksheet has list of names and locations. Names are unique, locations are repeated. e.g. 100 names working at Manchester, 50 names working at London, 123 at Glasgow etcetera.
    Alongside names are added values for the week, such as hours worked.

    I can set up custom views by location.
    I can share the workbook.
    What I don't want to do is make the complete list of information available to all of the viewers. i.e. London must not see manchester or glasgow, glasgow must not see manchester or london, and manchester must not see glasgow or london.

    Ideally I would look to have separate workbooks accessed by each of the locations.
    Locations must be able to view the data realtime
    Inputter could be inputting whilst locations are viewing.

    The scenario screams "use Access", but not all locations have access to access (inputter for one) and it seems be overkill.

    I am thinking to use a Splash which has user passnames to view data by location, but once the user has custom view open its a small step to view other locations data. (trust no-one is an adage which is based on past experience)
    I am also thinking to possibly use a splash on entry and then macro all of the relevant data to a single location worksheet, but how fast would this be for retrieving data from 3500 rows. Also, would this option still permit the inputter to continue inputting?

    Has anyone had a experience/solution to this in their repertoire.

    TIA
    Alan
    Cheshire, UK

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Viewing remote workbooks (97 sr2 on XP)

    Some comments:
    You can make it difficult to see the other info, but it won't be foolproof. How much you want to prevent it will dictate how much protection you want to go.

    What you do will depend on how you want the users to interact with the file. How many will need to open and edit at the same time? If only 1 person will want to edit at a time, you could have the others open it ReadOnly so there is never any file "in use" problems. If multiple will want to edit, having separate files seems better.

    You could one additonal file for the person who gets to see all of them and this could have code to open each one, extract the info, and compile it.

    If you have separate files for each location, if you are going to put them on a network drive, make separate folders for each, and set the security to only allow access to that folder for the appropriate people (WIndows NT and Windows2000 security is, I think, much better than excel's). This adds additional (non-excel) security to letting them see the other info.

    Steve

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Viewing remote workbooks (97 sr2 on XP)

    Couldn't the ideas you raised in <post#=349194>post 349194</post#> be applied? HTH
    Gre

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Viewing remote workbooks (97 sr2 on XP)

    I see them as different, but it could be used in this case also to add more protection. It could be used to only unhide certain sheets for the particular group. There still will be a problem if more than one user wants to edit at a time.

    Many of the files of info we use at work, only a few of us edit, the rest only have read only access. Yes, I know, the passwords aren't secure, but we only secure it so there is less chance of screwing up, not deliberate sabotage.

    Steve

  5. #5
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Viewing remote workbooks (97 sr2 on XP)

    OK, looked through the threads and I think it's coming down now to using Excel but not with custom views. (btw, populating list with custom views in a workbook reveals a MS bug whereby you have to create a temporary custom view in order to return the final custom view from your own customviews collection)

    So far then I have:
    created new SplashSheet (not a splash screen but merely a menu worksheet)
    created data worksheet
    on splashsheet I populate list from unique references for locations
    remove tabs from view
    on selecting item from list on splashsheet, the user is prompted for password,and if correct, the locations worksheet is unhidden and the relevant data returned from the data sheet to this location sheet. The macro to return the data writes(after deleting) approx 500 rows of information quite quickly (<2 secs). Because the tabs are hidden, the user cannot select any other worksheet. Button on location sheet sends user back to splashsheet.

    In line with comments about mistrusting others, the macro to return data to a separate sheet provides that small degree of separation needed to avoid corruption of the master data.

    Happy days are here again......

    Thanks again for all comments, I think I just needed a little impetus and alternative thought to keep me going.

    Alan

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Viewing remote workbooks (97 sr2 on XP)

    I assume you did more than merely hiding the tabs,control pageup and pagedown does work with hidden tabs.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Viewing remote workbooks (97 sr2 on XP)

    In addtion to Jan's comments, if you goto VB, from the immediate window you can also activate any sheet.

    Using VeryHidden sheets could help protect this better.

    Steve

  8. #8
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Viewing remote workbooks (97 sr2 on XP)

    With respect to the pageup pagedown scenario,
    I could add sub for on deactivate - worksheets("splashsheet").select (which I think would be wise to implement in any respect)
    Also, protect vb project with password, which should stop user gaining access to immediate window

    I'm now at the test stage where I have a reliable colleague to try and hack into the worksheet.
    If all goes well should be implemented by monday.

    Thanks again for everyones comments.
    Alan

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Viewing remote workbooks (97 sr2 on XP)

    Protecting a VB project will not prevent the user from accessing the immediate window. It only "prevents access" to the code of the "protected workbook". He has access to immediate window as well as being able to create a new workbook which could conceivably access the other sheets thru its own code.

    Tools -options is still available on protected workbooks and worksheets. So he could put the sheetnames back on.

    The code on deactivate will help to some extent, but in code you can always create a copy of any sheet that is not hidden:
    worksheets(1).copy

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •