It's maddening, I tell you (Locking cell FORMATS) 2

[tmktech]

Okay, I've lived with this long enough. There's GOT to be a way. I've got several Excel/VBA 2K apps and have locked up all formulas nicely, but here's my problem:

I've painstakingly formatted the sheets for user-friendliness / professionalism, but on unprotected fields, the user can screw up the formatting with a copy paste (instead of paste-special values).

Is there any way to lock up FORMATS in 2K on unprotected (i.e. data entry fields?) I need to let user enter formulas in there fields if they desire.

Am I missing something?

Thanks!

TMKTECH

 

[Zathras]

I wouldn't say you're missing something. It took me a while to come up with this...

Put this in the Sheet code:

Option Explicit
Dim SaveFormat As String

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Locked = False Then
    Target.NumberFormat = SaveFormat
  End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Target.Locked = False Then
    SaveFormat = Target.Cells(1, 1).NumberFormat
  End If
End Sub

Put this in a module:

Sub protectsheet()
  ActiveSheet.Protect Password:="123", UserInterfaceOnly:=True
End Sub

Use the macro to do the protection.
Read about "Protect Method" in the help file.
If the user copies into multiple cells, the format of the first cell in the copy-to range is used for all cells. If that is a problem for your situation, it would be possible to save/restore individual formats by cell address. (In arrays)

 

[tmktech]

Thanks, Zathras. That's a creative solution I didn't consider.

FYI - I've got the sheets password protected already, and when I lock/unlock in VBA I never use the userinterfaceonly:" param. When I tested it, it didn't seem to make any difference.

Once I plug this code in and test, I'll be throwing you a star.

THANKS!

 

[tmktech]

Well, that takes care of the number format. I now need to put in the code to cover all other fprmatting too (borders, cell color, font color, etc.) Is there a "global" format that I'm not seeeing? It appears that I'll need to save and restore interior.color, borderstyles, etc. individually?

TMKTECH

 

[Zathras]

Yes, I think you're right.

I'm curious about one thing, though. If your users are savvy enough be able to enter their own formulas, why not let them be responsible for colors, etc. too?

If it's borders you're concerned about, you could re-work the sheets so that only protected cells have borders. For example, instead of a box on C3, put left/right/top/bottom borders on the 8 surrounding (protected) cells. I know, it's a lot of work -- just a suggestion.

If your unprotected cells all have the same format, then it could be simplified a bit, too.

 

[RobBroekhuis]

If you really want this, you could have a second, hidden sheet, with all the formatting. Then, in your worksheet_change event, you can .copy the corresponding cell from the hidden sheet, and .pastespecial formats into the changed cell.
Rob

 

[Zathras]

TMKTECH, take a look at xlbo's response in thread68-520332. If you don't need to allow the user to cut and paste, it may provide the solution you are looking for.

 

[SteveBausch]

I really like what RobBroekhuis says about keeping a dup sheet to enforce formatting. I think that's about the only way short of yelling at the users.

I would prefer yelling at them, but it's frowned on.

 

[xlbo]

Rob - that's an absolute beauty - easy to implement as well
Like it ! Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde