Cell protection

G

Gregg Johnson

Is it possible to protect multiple cell ranges for different reasons? I
would like to protect certain cells to not allow any changes and I would like
to protect other ranges to allow data entry but not change the cell
formatting.

Thanks.
 
S

Scoops

Gregg said:
Is it possible to protect multiple cell ranges for different reasons? I
would like to protect certain cells to not allow any changes and I would like
to protect other ranges to allow data entry but not change the cell
formatting.

Thanks.
Click to expand...

Hi Gregg

As far as I know, when protecting the worksheet, you can protect all
the cells from having their formats changed or none of them.

But try Format>Conditional Formatting of the cells where you want to
allow editing but not format change e.g. In cell A1:
Formula Is =OR(A1="",A1<>"")
Apply the Format you want.

This should override a format applied by a user as long as the cell
contains nothing or something!

Regards

Steve
 
G

Gregg Johnson

Steve -

Thank you for your response. I'm having a little trouble with it:

When I attempt to change the format in conditional formatting, it will not
allow me to select Font or Size, only Style and Color. Is this typical?
Users will paste information into these cells. When I did that, it removed
the conditional formatting and used the pasted format. Is that to be
expected?

Thanks.
 
S

Scoops

Gregg said:
Steve -

Thank you for your response. I'm having a little trouble with it:

When I attempt to change the format in conditional formatting, it will not
allow me to select Font or Size, only Style and Color. Is this typical?
Users will paste information into these cells. When I did that, it removed
the conditional formatting and used the pasted format. Is that to be
expected?

Thanks.
Click to expand...

Hi Gregg

Yes, that's to be expected in both cases.

As a quick solution (I'm just about to finish for the day and this may
be the proverbial sledgehammer), try this:

Right-click the tab of the appropriate sheet > View Code and paste
this:

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

Regards

Steve
 
G

Gregg Johnson

Wow - that works great. Thanks.

Scoops said:
Hi Gregg

Yes, that's to be expected in both cases.

As a quick solution (I'm just about to finish for the day and this may
be the proverbial sledgehammer), try this:

Right-click the tab of the appropriate sheet > View Code and paste
this:

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

Regards

Steve
Click to expand...
 
S

Scoops

Gregg said:
Wow - that works great. Thanks.
Click to expand...
Glad to help, just be aware that the macro will work on every cell in
the worksheet so, if you do have some cells that you would like to be
altered by a paste action, you'll need to restrict where the macro
works.

For example, if you do not want it to affect cells in column A or row 2
or cell B3 then something like:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myValue
If Target.Column = 1 Or _
Target.Row = 2 Or _
Target.Address = "$B$3" Then Exit Sub
With Application
.EnableEvents = False
myValue = Target.Value
.Undo
Target = myValue
.EnableEvents = True
End With
End Sub

Regards

Steve
 
Top