Lock Only Format on Cells Excel 2003

sivan777 · 01-29-2009, 08:11 AM

Hi,

i have a problem with locking cell format. I understand how to lock a cells and lock a sheet. But i need this kind of combination.. I have lot of diferent cell formats on my sheets and i need people to add values inside, but i dont want them to be able to change a cell format.. (example date.. i have a lot of functions they works only if cell is in right date format.. and people they are using this sheet needs to paste a lot of values, and it is hard to explain them to use paste special.. valueas.. ) i need somehow lock those formats, but let users rewrite cell value.. i was trying almost everything but nothing really works.. thanks.

rbalaji · 01-29-2009, 09:00 AM

Don't lock any cells. Just protect the sheet. In the screen that comes up when you click tools->protection->protect sheet, make sure you uncheck "format cells".

sivan777 · 01-30-2009, 02:49 AM

thanks.. i will try it.

sivan777 · 01-30-2009, 02:50 AM

Hi, thank you for your answear, but i think we are not on the same page. If i lock sheet, it is valid only for cells they are marked as "locked" (in properties of cells). So if i unmark all cells and than lock a sheet it does not do anything.. i can change that date format... not with properties, but with copying and pasting it from different cell. This is more important to protect via pasting than regular format change by clicking properties.. my sheet has a lot of rows and people will paste a lot there.. if they dont use paste special.. they always paste format of cell as well. I need somethink what will allow them to paste it in, but not change that date format of cell.

In the case i lock cells, nobody can edit it.. so not working for me as well

One more time i really appreciate your time.

rbalaji · 01-30-2009, 09:52 AM

You are right, even though locking the way I suggested does not allow manual changes of the format, copying and pasting changes the format. I will try to do some more research into this, but I wonder if Excel can be told to do a paste of values without formats by default in certain sheets. That may be the solution if such a facility exists.

rbalaji · 01-30-2009, 09:57 AM

See if this solution will work for you.

sivan777 · 01-31-2009, 12:15 AM

Hi, thank you for your help.. I was really trying everything.. way i am thinking now is make some vba script which is checking paste function and always use only paste special.. but i am not very good with vba.. thanks one more time for your time.

rbalaji · 01-31-2009, 04:27 PM

Did you look at the solution I posted above? All you have to do is record a macro of using paste special->values and then associate ctrl+v as the hotkey for this macro so that the normal paste hotkey will trigger a paste special. Obviously, your users may use edit->paste from the menu, choose paste from the toolbar or right-click and choose paste, but you can eliminate these options by customizing the toolbar, etc. to eliminate those options.

nighttime · 06-15-2009, 04:54 AM

Nice article you found there rbalaji I was searching Google for a solution to basically the same problem sivan777 had. Got it fixed now was bugging me for a while thanks
__________________
Lock Replacement Services

01-29-2009, 08:11 AM	#1 (permalink)
sivan777 Registered User Join Date: Jan 2009 Posts: 4 OS: xp sp3	Lock Only Format on Cells Excel 2003 Hi, i have a problem with locking cell format. I understand how to lock a cells and lock a sheet. But i need this kind of combination.. I have lot of diferent cell formats on my sheets and i need people to add values inside, but i dont want them to be able to change a cell format.. (example date.. i have a lot of functions they works only if cell is in right date format.. and people they are using this sheet needs to paste a lot of values, and it is hard to explain them to use paste special.. valueas.. ) i need somehow lock those formats, but let users rewrite cell value.. i was trying almost everything but nothing really works.. thanks.

01-29-2009, 09:00 AM	#2 (permalink)
rbalaji Tech, Microsoft Support Join Date: Dec 2008 Location: Chicagoland Posts: 1,193 OS: Windows XP SP3	Re: Lock Only Format on Cells Excel 2003 Don't lock any cells. Just protect the sheet. In the screen that comes up when you click tools->protection->protect sheet, make sure you uncheck "format cells". __________________ Balaji Ramanathan Computers - You can't live with them, you can't live without them!

01-30-2009, 02:49 AM	#3 (permalink)
sivan777 Registered User Join Date: Jan 2009 Posts: 4 OS: xp sp3	Re: Lock Only Format on Cells Excel 2003 thanks.. i will try it. Last edited by sivan777; 01-30-2009 at 02:51 AM.

01-30-2009, 02:50 AM	#4 (permalink)
sivan777 Registered User Join Date: Jan 2009 Posts: 4 OS: xp sp3	Re: Lock Only Format on Cells Excel 2003 Hi, thank you for your answear, but i think we are not on the same page. If i lock sheet, it is valid only for cells they are marked as "locked" (in properties of cells). So if i unmark all cells and than lock a sheet it does not do anything.. i can change that date format... not with properties, but with copying and pasting it from different cell. This is more important to protect via pasting than regular format change by clicking properties.. my sheet has a lot of rows and people will paste a lot there.. if they dont use paste special.. they always paste format of cell as well. I need somethink what will allow them to paste it in, but not change that date format of cell. In the case i lock cells, nobody can edit it.. so not working for me as well One more time i really appreciate your time.

01-30-2009, 09:52 AM	#5 (permalink)
rbalaji Tech, Microsoft Support Join Date: Dec 2008 Location: Chicagoland Posts: 1,193 OS: Windows XP SP3	Re: Lock Only Format on Cells Excel 2003 You are right, even though locking the way I suggested does not allow manual changes of the format, copying and pasting changes the format. I will try to do some more research into this, but I wonder if Excel can be told to do a paste of values without formats by default in certain sheets. That may be the solution if such a facility exists. __________________ Balaji Ramanathan Computers - You can't live with them, you can't live without them!

01-30-2009, 09:57 AM	#6 (permalink)
rbalaji Tech, Microsoft Support Join Date: Dec 2008 Location: Chicagoland Posts: 1,193 OS: Windows XP SP3	Re: Lock Only Format on Cells Excel 2003 See if this solution will work for you. __________________ Balaji Ramanathan Computers - You can't live with them, you can't live without them!

01-31-2009, 12:15 AM	#7 (permalink)
sivan777 Registered User Join Date: Jan 2009 Posts: 4 OS: xp sp3	Re: Lock Only Format on Cells Excel 2003 Hi, thank you for your help.. I was really trying everything.. way i am thinking now is make some vba script which is checking paste function and always use only paste special.. but i am not very good with vba.. thanks one more time for your time.

01-31-2009, 04:27 PM	#8 (permalink)
rbalaji Tech, Microsoft Support Join Date: Dec 2008 Location: Chicagoland Posts: 1,193 OS: Windows XP SP3	Re: Lock Only Format on Cells Excel 2003 Did you look at the solution I posted above? All you have to do is record a macro of using paste special->values and then associate ctrl+v as the hotkey for this macro so that the normal paste hotkey will trigger a paste special. Obviously, your users may use edit->paste from the menu, choose paste from the toolbar or right-click and choose paste, but you can eliminate these options by customizing the toolbar, etc. to eliminate those options. __________________ Balaji Ramanathan Computers - You can't live with them, you can't live without them!

06-15-2009, 04:54 AM	#9 (permalink)
nighttime Registered User Join Date: Jun 2009 Posts: 1 OS: Vista	Re: Lock Only Format on Cells Excel 2003 Nice article you found there rbalaji I was searching Google for a solution to basically the same problem sivan777 had. Got it fixed now was bugging me for a while thanks __________________ Lock Replacement Services Last edited by Glaswegian; 06-15-2009 at 06:17 AM. Reason: Removed advertising link in sig - please read the Rules!!