Forum Discussion
Unable to lock cell formatting
perkin_warbeck yes you are correct and I'm sure you read my sample code. I delete any formats they brought in and then added the new formats. I apply the new formats to all pasted cells but sounds like you have to be more selective.
How about a completely different approach where you have them paste the data in a different sheet and then you use VBA to copy and paste values only.
Or if they past in a different sheet then you just use a formula to reference that data from the sheet with the formats.
You can also use on selection in VBA to determine where they want to paste the data and then use VBA to paste values or give them a pop-up. You can lock the entire sheet but allow selection so they can paste anywhere but VBA would do the work instead.
There are many ways to skin a cat in excel, it's hard to suggest the best option for you without knowing more about the application.
perkin_warbeck yes you are correct and I'm sure you read my sample code. I delete any formats they brought in and then added the new formats. I apply the new formats to all pasted cells but sounds like you have to be more selective.
How about a completely different approach where you have them paste the data in a different sheet and then you use VBA to copy and paste values only.
Or if they past in a different sheet then you just use a formula to reference that data from the sheet with the formats.
You can also use on selection in VBA to determine where they want to paste the data and then use VBA to paste values or give them a pop-up. You can lock the entire sheet but allow selection so they can paste anywhere but VBA would do the work instead.
There are many ways to skin a cat in excel, it's hard to suggest the best option for you without knowing more about the application.
mtarler I should have mentioned that I am very new to VBA, so I'm trying out most of these things for the first time. I think you have pointed me in a a couple of good directions.
Repairing the formatting is certainly one way to do it. I don't mind the application being complex if it spares the user from having to remember to Paste Values.
But you say "You can also use on selection in VBA to determine where they want to paste the data and then use VBA to paste values." It's an interesting solution, but it would require another button to accomplish the paste. I'd like users to be able to paste normally.
I will mark your response as "Best Response" because you have given me several good ideas. Thanks!