Protecting formats

  • Thread starter Thread starter Mikey May
  • Start date Start date
M

Mikey May

Does anyone know how I can protect the format of cells
e.g. borders, number formats, etc within a spreadsheet but
still allow cells to be dragged, copied, etc.

I have a spreadsheet that is being used and abused and
looks a real mess at the end of everyday.


Thanks
 
Mikey May said:
Does anyone know how I can protect the format of cells
e.g. borders, number formats, etc within a spreadsheet but
still allow cells to be dragged, copied, etc.

I have a spreadsheet that is being used and abused and
looks a real mess at the end of everyday.
Click to expand...

You have contradictory requirements.
Dragging cells is the same as moving them. When you move a cell, you move
its formatting with it.
If you don't want to move the formats, don't move the cells. Copy and paste
just the data (i.e. Paste Special > Values) and then (if necessary) clear
the data from the original cell (using the Delete key on the keyboard).

An alternative would be to not have formatting on this worksheet, so you can
do what you want. Then you could have another (protected, if you wish)
worksheet formatted as you want, with formulas in each cell linking to cells
on the first sheet. Use the INDIRECT function so that the formulas don't
adjust if you move cells on the first worksheet, like this:
=INDIRECT("Sheet1!A1")
 
-----Original Message-----


You have contradictory requirements.
Dragging cells is the same as moving them. When you move a cell, you move
its formatting with it.
If you don't want to move the formats, don't move the cells. Copy and paste
just the data (i.e. Paste Special > Values) and then (if necessary) clear
the data from the original cell (using the Delete key on the keyboard).

An alternative would be to not have formatting on this worksheet, so you can
do what you want. Then you could have another (protected, if you wish)
worksheet formatted as you want, with formulas in each cell linking to cells
on the first sheet. Use the INDIRECT function so that the formulas don't
adjust if you move cells on the first worksheet, like this:
=INDIRECT("Sheet1!A1")


.
Click to expand...
The spreadsheet is for other users who don't use/know
about Paste Special, Values.

It would be nice just to have a macro that upon opening
would only allow input into cells raher than any ability
to format whether dragging or copy, pasting, etc.
 
Mikey,
What type of info are the users cutting and pasting? Since the data is already on the sheet, maybe you could use Data Validation so the users select from a list instead of cut and copy.

Good Luck,
Mark Graesser
(e-mail address removed)


----- Mikey May wrote: -----

Does anyone know how I can protect the format of cells
e.g. borders, number formats, etc within a spreadsheet but
still allow cells to be dragged, copied, etc.

I have a spreadsheet that is being used and abused and
looks a real mess at the end of everyday.


Thanks
 
-----Original Message-----
Mikey,
What type of info are the users cutting and pasting?
Click to expand...
Since the data is already on the sheet, maybe you could
use Data Validation so the users select from a list
instead of cut and copy.
Good Luck,
Mark Graesser
(e-mail address removed)


----- Mikey May wrote: -----

Does anyone know how I can protect the format of cells
e.g. borders, number formats, etc within a spreadsheet but
still allow cells to be dragged, copied, etc.

I have a spreadsheet that is being used and abused and
looks a real mess at the end of everyday.


Thanks

.
Click to expand...
Not as easy as that Mark. Tried that and everyone
complained it was taking to long, and data is pretty much
unique for each cell except for following. Problem arises
when duplicate info required for multiple rows and is
copied down and it pull the formats with it.

Im sure there must be some sort of function macro that
will let you pull data from ajacent cells while retaining
the formats. But hey ho.......
 
Maybe you could just reapply the format everytime they make a change??

Copy the worksheet to a new name: HiddenFormat is what I used.

In a general module, protect your worksheet (sheet1), but allow your macros to
do more stuff than the users can:

Option Explicit
Sub auto_open()
With Worksheets("sheet1")
.Protect Password:="hi", userinterfaceonly:=True
End With
End Sub


And right click on the worksheet tab and paste this in:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myArea As Range

With Application
.EnableEvents = False
.ScreenUpdating = False
End With

For Each myArea In Worksheets("HiddenFormat").Range(Target.Address).Areas
myArea.Copy
Me.Range(myArea.Address).PasteSpecial Paste:=xlPasteFormats
Next myArea

With Application
.EnableEvents = True
.CutCopyMode = False
End With

End Sub

But make sure you keep the worksheets in sync. If you change the format on one,
change the format on the other!

And hide the HiddenFormat so no one can see it.

=====
An alternative to doing the formatting with each change would be to apply the
formatting right before they save the workbook. You may want to stick the
equivalent code in that event.
 
If your users can't use paste_special, they going to have a lot more trouble using macros. I have two other thoughts that might help you

1) When you pull the Fill Handle with your right mouse button you will get a menu that lets you select Fill Value

2) Maybe you can create a hidden sheet that retains the formatting, and record a simple macro that will copy the hidden sheet and Paste_Special>Formats over the front sheet. You could then assign it to a button to make it easy for all of the users

Good Luck
Mark Graesse
(e-mail address removed)

----- (e-mail address removed) wrote: ----

-----Original Message----
Mikey
What type of info are the users cutting and pasting?
Click to expand...
Since the data is already on the sheet, maybe you could
use Data Validation so the users select from a list
instead of cut and copy
Mark Graesse
(e-mail address removed) cells
e.g. borders, number formats, etc within a spreadsheet but
still allow cells to be dragged, copied, etc and
looks a real mess at the end of everyday
Click to expand...

Not as easy as that Mark. Tried that and everyone
complained it was taking to long, and data is pretty much
unique for each cell except for following. Problem arises
when duplicate info required for multiple rows and is
copied down and it pull the formats with it

Im sure there must be some sort of function macro that
will let you pull data from ajacent cells while retaining
the formats. But hey ho......
 
Back
Top