Go Back   Tech Support Forum > Microsoft Support > Microsoft Office support

Lock Only Format on Cells Excel 2003

This is a discussion on Lock Only Format on Cells Excel 2003 within the Microsoft Office support forums, part of the Tech Support Forum category. Hi, i have a problem with locking cell format. I understand how to lock a cells and lock a sheet.


Closed Thread
 
Thread Tools Search this Thread
Old 01-29-2009, 07:11 AM   #1
Registered Member
 
Join Date: Jan 2009
Posts: 4
OS: xp sp3



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.

__________________
sivan777 is offline  
Old 01-29-2009, 08:00 AM   #2
Registered User
 
rbalaji's Avatar
 
Join Date: Dec 2008
Location: Chicagoland
Posts: 1,193
OS: Windows XP SP3



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!
rbalaji is offline  
Old 01-30-2009, 01:49 AM   #3
Registered Member
 
Join Date: Jan 2009
Posts: 4
OS: xp sp3



thanks.. i will try it.
__________________
sivan777 is offline  
Old 01-30-2009, 01:50 AM   #4
Registered Member
 
Join Date: Jan 2009
Posts: 4
OS: xp sp3



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.
__________________
sivan777 is offline  
Old 01-30-2009, 08:52 AM   #5
Registered User
 
rbalaji's Avatar
 
Join Date: Dec 2008
Location: Chicagoland
Posts: 1,193
OS: Windows XP SP3



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!
rbalaji is offline  
Old 01-30-2009, 08:57 AM   #6
Registered User
 
rbalaji's Avatar
 
Join Date: Dec 2008
Location: Chicagoland
Posts: 1,193
OS: Windows XP SP3



See if this solution will work for you.
__________________
Balaji Ramanathan

Computers - You can't live with them, you can't live without them!
rbalaji is offline  
Old 01-30-2009, 11:15 PM   #7
Registered Member
 
Join Date: Jan 2009
Posts: 4
OS: xp sp3



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.
__________________
sivan777 is offline  
Old 01-31-2009, 03:27 PM   #8
Registered User
 
rbalaji's Avatar
 
Join Date: Dec 2008
Location: Chicagoland
Posts: 1,193
OS: Windows XP SP3



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!
rbalaji is offline  
Old 06-15-2009, 03:54 AM   #9
Registered Member
 
Join Date: Jun 2009
Posts: 1
OS: Vista



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

__________________
nighttime is offline  
Closed Thread

Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is on
Smilies are on
[IMG] code is on
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


Post a Question


» Site Navigation
 > FAQ
  > 10.0.0.2


All times are GMT -7. The time now is 08:18 AM.


Copyright 2001 - 2014, Tech Support Forum

Windows 7 - Windows XP - Windows Vista - Trojan Removal - Spyware Removal - Virus Removal - Networking - Security - Top Web Hosts