Protecting Cells In Shared Workbook

Re: Protecting Cells In Shared Workbook

you can simply add a workbook change event.

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

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.

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

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]