Locking format in 2000

Todd Bardoni

Well-known Member
Joined
Aug 29, 2002
Messages
3,042
Is there a way to lock the format in Excel 2000 and still let people type in the cell? I know you can do it in XP, but I use 2000 at work.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Todd Bardoni

Well-known Member
Joined
Aug 29, 2002
Messages
3,042
I re-read my question and I didn't fully explain. I got a problem with people dragging the format from one cell to another or sometimes they copy and paste (accidentally) the foramt. I unlock the cells, lock the document, but people are still able to drag or copy the formatting from and to the unprotected cells. I want people to be able to type in the cells but not move the formatiing.
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
This may or may not help, it resets the format of any cell or range changed on a sheet, to the default format automatically. It will not let you change, or copy a new format to any cell or range. The code go's into the sheet module for the sheet you want to prevent formating changes to. JSW

Private Sub Worksheet_Change(ByVal Target As Range)
'Reset cell formatting to the default settings.
Selection.NumberFormat = "General"
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
With Selection.Interior
.ColorIndex = 0
.Pattern = xlNone
End With
End Sub
 

joy

New Member
Joined
Mar 3, 2005
Messages
12

ADVERTISEMENT

:rolleyes: I have a similar problem. I am copying and pasting from websites, but each time I paste into a cell, it changes to the format of the website. Like a good litte forum girl, I have read up on others' problems similar to mine and tried the suggestions, but with each, some part doesn't serve my purposes.

I've done the whole Unlock some cells then Protect the sheet deal. First, this isn't feasible because other people must be able to add rows to the sheet and edit the cells, and the cells with a lot of text will not scroll through to the bottom if the cell IS locked. Also, even when I did this, I tested copying something into one of the unlocked cells and once again, the format changed.

Paste Special: Text only does not work because each Paste is several lines in the website, so it pastes to several cells in a column instead of all into one cell. :oops:

At the VERY LEAST, how do i get it so that excel doesn't automatically wrap text in the cells? (although this, too, might because of the cut/paste).
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
The only way I know of is with the Change Event, something like above. So when a cell gets it's format changed the Event code re-does the formatting so the cell once again is formatted the way you would like it to be formatted. Rather than how the data has been formatted.

In the change event you can restrict this to certain ranges and have different formats for different ranges!
 

joy

New Member
Joined
Mar 3, 2005
Messages
12

ADVERTISEMENT

I'll try that, thank you.

I also forgot to mention that I have access to XP and 2003 excel (where
I know there is specific "donot allow them to change format" options), but not on this computer. If I START the sheet on one of those versions, and THEN come back to the office computer with excel 2000, will it still hold? Stupid question? joy
 

joy

New Member
Joined
Mar 3, 2005
Messages
12
Hi all, just an update to tell you what works:
Yes, the code works wonderfully, thank you!
Paste special, Values only does not - it still wants to put each line in a different cell, so I'm not doing that.
In addition, I have found that if I preset the rows to a designated height, even if the formatting sticks, it still does not expand the cell.

I love this forum!
Joy
 

Forum statistics

Threads
1,143,708
Messages
5,720,388
Members
422,282
Latest member
psunith

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top