Solved: How to prevent user from selecting other background color

[Digita]

Greetings,

Hope you can help.

I have a workbook in which users have marked certain cells with a particular color to ensure the contents are kept intact from any macro execution.

Obviously any code that changes the contents of a range would contain a line at front end to evaluate the interior colorindex number of each cell before updating same.

Changing cell fill color can also be done by format pasting. Would there be any way to restrict the selection of color to only 2 options: no fill color and a particular color only?

Thanks in advance.

Regards

[GTO]

Changing cell fill color can also be done by format pasting. Would there be any way to restrict the selection of color to only 2 options: no fill color and a particular color only?



When you say 'restrict the selection', I figure you mean restrict certain colors from displaying or being selectable from the visual palatte. I don't like saying 'never', but I would say there's easier ways.

Depending on the project, you might go as far as to subbing in different toolbars/commands and hiding/disabling the app's command bars. Assuming you'd like something simpler, here is an attached example, wherein we use the ENVIRON variable UserName to see who's logged on. If it ain't you, we protect the sheet from formatting... but... we install a button that will toggle the active cell's interior (colorindex).

Look at the code under ThisWorkbook and the sheet module. I did a very quick adaptation for the example, but hopefully it is clear enough to glean...

Hope this helps!

Mark

Function Protection_Set()
If UCase(Environ("USERNAME")) = "STUMPM" Then '< Put your username here.
For Each wksWorksheet In ThisWorkbook.Worksheets
wksWorksheet.Unprotect PWord
wksWorksheet.Protect PWord, True, True, True, True, True
Next
Else
For Each wksWorksheet In ThisWorkbook.Worksheets
wksWorksheet.Unprotect PWord
wksWorksheet.Protect PWord, True, True, True, True, False
Next
End If
End Function

[Digita]

Thanks Mark for your quick suggestion.

Sheet protection and toggle button to change colorindex is a simpler workaround.

Best regards


kp

[GTO]

Glad it worked for you :friends: