Protecting Cells In Shared Workbook

  • hi guys and girls
    Need you help once again
    I have a shared workbook is it at all possible to leave the cells unprotected so a user can enter data but prevent cells from being formated


    for instance someone on night shift with nothing better to do has decided that black text on a white background is boring and changes it frequently to a rainbow of colors. and text types
    it is making a well laid out and good sheet look like something a child has done
    I would pursue this down a discipline line but it has proved impossible to nail down the culprit from the many users


    Thanks
    Tim

  • Re: Protecting Cells In Shared Workbook


    you can simply add a workbook change event.


    Code
    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
        Cells.Select
        Selection.Font.ColorIndex = 0
    End Sub


    this will automatically change the font colors back to black in all cells on active sheet.


    Or you can assign it to do it for the entire workbook


    Code
    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
        Dim ws as worksheet
        For Each ws In ActiveWorkbook.Sheets
        Cells.Select
        Selection.Font.ColorIndex = 0
        Next ws
    End Sub
  • Re: Protecting Cells In Shared Workbook


    just realized this is no good. will never let you select a cell. let me think about it for a little bit.


    I am sure someone else here has an idea though...

  • Re: Protecting Cells In Shared Workbook


    Nevermind...I am an idiot...use either code below.


    Code
    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    application.screenupdating = false
        Cells.Font.ColorIndex = 0
    application.screenupdating = true
    End Sub


    Or

    Code
    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
        Dim ws as worksheet
    application.screenupdating = false
        For Each ws In ActiveWorkbook.Sheets
        Cells.Font.ColorIndex = 0
        Next ws
    application.screenupdating = true
    End Sub
  • Re: Protecting Cells In Shared Workbook


    your very welcome. best of luck with your employee search.


    FYI...I am sure you can create a log file of users using a very easy if then statement that could verify who your "bad apple" is.


    Of course you would have to not use the code posted above for a while so that the user could make the font color changes. I have done this in the past and it worked like a charm.

  • Re: Protecting Cells In Shared Workbook


    I have a similar issue but with a twist. I need to protect cells which my team members can't edit and only the manager can and at the same time the work book is shared hence ...

  • Re: Protecting Cells In Shared Workbook


    use code in the open workbook event to lock or unlock cells based on the application.username value.

    This is not foolproof (users can change the username value) but it should work. Alternatively you can call the Network login ID through windows API calls, and that should be foolproof, but its much more complex.

  • Re: Protecting Cells In Shared Workbook


    Hello aalokjain,


    Welcome to Ozgrid. While we're glad to have you on-board, please note:


    Posting your question in threads started by others is a violation of the forum rules and is known as thread hijacking. Posting solutions is acceptable.
    ALWAYS start a new thread for your question and if you think it is helpful to clarify your thread you may include a link back this thread (or any other).


    Start a new thread and be sure to give it a title that complies with the following guidelines;


    Thread titles are used in searching the forum, therefore, it is vital they be written to accurately describe your [COLOR="blue"]thread content or overall objective[/COLOR] using ONLY search friendly key words. That is, your title use as search terms would return relevant results.


    • The title must not use non-essential words such as:"Help needed", "Formula problem", "Please help", "urgent", "Code issue", "Need Advice", etc. Such words dilute the title/search results.
    • The title should not contain VBA code or formula syntax or use abbreviations, jargon, delimiters (e.g. slashes, commas, colons, etc)
    • The title should not assume or anticipate a solution as in referencing Excel functions or VBA methods - the actual solution is often quite different


    [COLOR="darkred"]Please note the change to your title, which is based on the objective stated in your thread.[/COLOR]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!