Art

The paste options button allows you to "match destination" formatting.

If you don't want to use that you can use VBA.

Try this event code which retains all cell formatting when something is
copy/pasted into it.

Cells must be pre-formatted to centered and centered as you state.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myValue
With Application
.EnableEvents = False
myValue = Target.Value
.Undo
Target = myValue
.EnableEvents = True
.CutCopyMode = False
End With
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code".

Copy/paste the code into the module.

Operates only on the one sheet.

If you wanted for all sheets use this code which is to placed in
Thisworkbook module, not a sheet module.

Private Sub Workbook_SheetChange(ByVal Sh As _
Object, ByVal Target As Range)
Dim myValue
With Application
.EnableEvents = False
myValue = Target.Value
.Undo
Target = myValue
.EnableEvents = True
.CutCopyMode = False
End With
End Sub


Gord Dibben MS Excel MVP



On Mon, 15 Mar 2010 15:09:01 -0700, Art
wrote:

I know how to format a cell so the alignment is centered both vertically and
horizontally.

However, if a user copies/pastes text from, say, Microsoft Word into a cell
in Excel, it positiions it to the left at the bottom of the cell. Not a big
deal, really, but I would like for all of the cells to be centered
horizontally and vertically.

Is there a way to force a cell to align its contents centered, even if it is
copied and pasted from another document, rather than manually entered with a
cursor inside the cell? (Perhaps a macro could check a cell once the enter
key is pressed or the focus leaves a cell in some way?)

Thanks!