+ Reply to Thread
Results 1 to 6 of 6

Making spreadsheets for other users

  1. #1
    Forum Contributor
    Join Date
    06-19-2013
    Location
    UK
    MS-Off Ver
    Excel 2016
    Posts
    144

    Making spreadsheets for other users

    Hello. I'm going to be making some spreadsheets for other people to use at my work, problem is these other people know very little about excel. Plus I've only ever made spreadsheets for myself before.

    What I'm looking for is a bit of help, to be pointed in the right direction for certain things

    For example.
    I've looked up the way you can lock cells and protect sheets, that's certainly going to be a big help so no-one will delete my formulas

    I'm also hoping to create sheets solely where they will input the data, and then that data can be added to a seperate sheet which contains all previous data (does this method have a name?)
    I've done this before using macros, but some of the spreadsheets I'll be making will be on a network used by many different computers so I'm hoping to keep macros down to a minimum

    Also what macros can you get away with without the default excel security settings warning users. Where's the best place I can look into macro security


    And if anyone's got any advice or something I should look into for making spreadsheets to be used by other people will be greatly appreciated.

  2. #2
    Registered User
    Join Date
    06-16-2013
    Location
    Yorkshire
    MS-Off Ver
    Excel 2013
    Posts
    33

    Re: Making spreadsheets for other users

    Far as I know, ALL macros will trigger Excel's security warning unless the user has such warnings disabled. (most won't, and it's not recommended anyway)

    What sorts of things are you wanting to achieve with macros? Sometimes the same thing can be achieved using formulas instead.

    As for things to look into, if your users aren't used to excel then I recommend looking at data validation in addition to locked/protected sheets. Someone putting 'notes' in fields that you'd prefer numbers only can be really annoying. Or even just simple spelling mistakes for common data that could instead have a dropdown list.

  3. #3
    Forum Contributor
    Join Date
    06-19-2013
    Location
    UK
    MS-Off Ver
    Excel 2016
    Posts
    144

    Re: Making spreadsheets for other users

    Thanks for the reply. I've looked into that data validation and that will certainly be helpful. Thank you

    As for macros. I was hoping that using basic stuff like clearing certain cells, copying cells into a seperate sheet for printing, could be done without a security alert from excel.

    Having the sheet that's designed for printing without macros should be no problem. However the sheet that contains all previously entered values, I don't know how I would get the values entered in the user friendly sheet, to be added to the bottom row each time without macros

  4. #4
    Forum Contributor noboffinme's Avatar
    Join Date
    08-29-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003/7/10/13/16/19
    Posts
    1,067

    Re: Making spreadsheets for other users

    Hi Smally,

    You shouldn't have a problem with your Macros on a Network drive so why don't you leave them in your file?

    I agree with the above ideas & I would add one more to make it easier for the users - shade any Columns background (in any colour) that contain Formulas OR shouldn't be changed by your users.

    This will help them follow where they should add the data & not touch anything else.

    Cheers
    Remember you are unique, like everyone else

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Making spreadsheets for other users

    Also what macros can you get away with without the default excel security settings warning users. Where's the best place I can look into macro security
    None. And for versions 2007 and later, you will need to have macros enabled on all machines that will be using your files

    Advice - have a data entry sheet, make regular backups and hide/protect all the stuff you dont want them to mess with. Also, work onthe premise that - if there is a way to screw things up, someone will find that way. It's been my experience that as far as error-proofing is concerned, its a catch-up game...you fix 1 loophole, they find another lol

    cynical? you bet, comes from "been there, done that" lol
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  6. #6
    Forum Contributor
    Join Date
    06-19-2013
    Location
    UK
    MS-Off Ver
    Excel 2016
    Posts
    144

    Re: Making spreadsheets for other users

    Advice - have a data entry sheet, make regular backups and hide/protect all the stuff you dont want them to mess with. Also, work onthe premise that - if there is a way to screw things up, someone will find that way. It's been my experience that as far as error-proofing is concerned, its a catch-up game...you fix 1 loophole, they find another lol
    Yep I'm having to protect sheets so they stop messing.

    I there anyway to have a data entry sheet without the use of macros?
    I was originally hoping to assign macros to buttons so it would add and sort data auto for the users. But with multiple people in multiple computers I don't want to have to explain to everyone to not worry about the security warning

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Possible to restrict certain users from making changes?
    By CashmereCat in forum Excel General
    Replies: 1
    Last Post: 11-02-2012, 11:27 AM
  2. Making VBA code not viewable to users
    By cicoff in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-13-2009, 06:55 PM
  3. Forcing users to update spreadsheets
    By DKerr in forum Excel General
    Replies: 2
    Last Post: 07-27-2006, 07:55 AM
  4. [SOLVED] Making changes to the same worksheet by two users at the same time
    By Monika Graczyk in forum Excel General
    Replies: 1
    Last Post: 05-17-2006, 05:45 AM
  5. Making sheets visible and hidden to users
    By Xlyr in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-06-2005, 04:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1