Forum Discussion
protect borders
Hello, is there a way to protect borders on excel? We have forms that have different thicknesses in the borders and when people copy and paste, the thickness can change, which makes the whole form look goofy. Is there a way to let them copy and paste where the border doesn't change?
Thanks
- It's so annoying that you can't protect borders, as they are the mainstay of Excel. Pasting Values is such an inconvenience when I am using large spreadsheets, and I like to use coloured shaded cells a LOT. I have gotten used the my borders being incomplete everywhere - it's such an unprofessional look.
Set up a separate worksheet with the proper formatting for all cells.
(no values or formulas)
Hide the sheet.
Write VBA code to paste the hidden sheet formatting into the messed up sheet.
(just a few lines required)
Add a button to the QAT to run the VBA code or it could be done automatically before each Save.'---
Nothing Left to Lose
https://1drv.ms/u/s!Au8Lyt79SOuhZw2MCH7_7MuLj04?e=sAwbHU
(free excel programs)
- If they do Paste Values the borders won't change
- If you know VBA then you can refer to the code in my response here
https://techcommunity.microsoft.com/t5/Excel/A-way-to-totally-and-reliably-block-PASTE-operations-in-Excel/td-p/488124
Hi ginalk2019
I don't believe there's a way to specifically protect borders on Excel. But if anyone knows of this, please let us know!
The workaround for this, as mentioned by Wyn Hopkins, is to copy your source data and then paste special them into the form.
You can do this via several methods.
After copying your data, you can highlight the data region which you wish to dump your data on, and:1) Right-click with the mouse, and select paste special (values only), or;
2) Use the keyboard shortcut, Alt + E + S + V +Enter
Both of these should work for the problem you're facing right now!
Best,
JustinJustin, few more
If by mouse,
-Add to Quick Access Toolbar icons Copy and Paste Values (Copy is only not to jump from ribbon to QAT) and train people to use them instead of ribbon Copy/Paste.
- right click selected cell/range border, drag it to the destination place and Copy Here as Values Only from appeared, after you release right button, menu.
If by keyboard, Ctrl+C => Ctrl+V, Ctrl, Down, Down, Enter.
And I still prefer Ctrl+Ins/Shift+Ins instead of Ctrl+C/Ctrl+V.
- My 2 favourites are
Ctrl c, Ctrl v followed by Ctrl then v
Or
Ctrl c, then right click button on keyboard followed by v
- As previously mentioned I don't think there is any way of protecting cell borders. The issue with asking people to use paste special is they always forget, so that doesn't really work either. What about a macro that runs on closing the workbook that will reinstate the borders? I assume that the extent of the borders doesn't change much within the form, just the information.
There is a way indeed. Set up conditional formatting to suit your needs (for example no borders when the cells are not blank) and then protect the worksheet.
