Cell protection

[Guest]

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.

 

[Scoops]

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.

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

 

[Guest]

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.

 

[Scoops]

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.

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

 

[Guest]

Wow - that works great. Thanks.

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

 

[Scoops]

Wow - that works great. Thanks.

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