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.
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"
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
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
.ColorIndex = 0
.Pattern = xlNone
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.
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).
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!
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
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.