Retaining conditonal formats

[MM]

Hi, I need some help please!

I've designed a sheet to collect data from users which has been set up with
lots of set formats (e.g round £ to two decimal places). That's fine and I'm
happy with what it's doing.

The problem that I'm having is that some users are just copying and pasting
chunks of data from other data bases into the sheet and I'm loosing all the
set formats that have set up.

My question is: How do I allow users to both, type the data in, or paste
data in and the sheet to retain the formats that I've set up! Any help or
suggesions would be appreciated.

Many thanks,

MM

 

[OssieMac]

Hi MM,

This can be done with code in the Workbook Open event. When you have set up
the worksheet with the formats that you want, you copy the Worksheet so that
you have a duplicate. Then in the Workbook open event you copy the duplicate
sheet and Paste Special -> Formats into the main worksheet.

The duplicate worksheet can be hidden. If hidden with code it can be
VeryHidden so that the users cannot unhide it without code.

If you want such a method, then let me know and I can provide the code. Let
me know if you require instructions to copy the code into your workbook. Also
what version of Excel you are using so I can include instructions for
enabling macros.

Note that the method does not prevent the users changing formats; it just
returns the correct formats when the workbook is opened.

If you don't want the macro code in the workbook that all of the users have,
then it can be placed in a separate workbook together with the copy of the
worksheet and then you can open the workbook via the one with the code. Let
me know what you want. If you want this method then let me knw the full name
of the main workbook.

 

[Gord Dibben]

Add this event code to the worksheet module.

Right-click and "View Code" to access the module.

Private Sub Worksheet_Change(ByVal Target As Range)
'retain formatting when a cell is copied over
Dim myValue
With Application
.EnableEvents = False
myValue = Target.Value
.Undo
Target = myValue
.EnableEvents = True
End With
Application.CutCopyMode = False
End Sub


Gord Dibben MS Excel MVP

 

[OssieMac]

I like the idea Gord but using Target.Value prevents a formula being entered
on the worksheet. Using Target.Formula seems to work better.

myValue = Target.Formula
.Undo
Target.Formula = myValue

 

[Gord Dibben]

Good point.

Thanks