VBA to Disable Format Painter Tool.

[vishalrattan]

Hi All,

Hope you are well, I have a little issue which for the life of me cannot get around.



I would like to disable the Format Painter tool hopefully by VBA.

Background:

I have a protected worksheet, but users need the ability to FILL in the cells. However these cells have individual conditional formatting which will get moved around if they decided to use the Format painter tool. Hence my need for them to only have the ability and access to the FILL colour function.
Alternatively, If there is an ability to disable all types of formatting and only solely the FILL Colour function available.

Hope this helps.

Thanks for your help in advanced.

[leelnich]

Hi vishalrattan - I haven't worked with protected worksheets much, but you could lock the sheet and use a macro to show the Fill Dialog box automatically whenever they select a cell or cells. Right-click on your worksheet Tab, select View Code, and paste the following code in the module that appears. (You'll need to change the password to match yours.) When done, press ALT+F11 to return to Excel.

Please Login or Register  to view this content.

The code could easily be modified to respond only to specific cells.

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee