Hi all,

I have used the "EnableSelection = xlUnlockedCells" to Protect some worksheets, but this allows the
cells to be reformatted. What I want are cells that can only have their values changed and not
allow them to have their formatting altered...this includes copying or cutting and pasting.

How can this be accomplished using VBA code in a code segment like:

With Worksheets(Indx)
.Unprotect

' do my stuff here...

.EnableSelection = xlUnlockedCells
.Protect
End With


TIA,

Rick

Maybe you could just intercept the change. This worked in light testing.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myFormulas As Variant

On Error GoTo errHandler:

myFormulas = Target.Formula
With Application
.EnableEvents = False
.Undo
End With
Target.Formula = myFormulas

errHandler:
Application.EnableEvents = True

End Sub

rightclick on the worksheet tab that should have this behavior. Select View
code and paste this in.

(But I couldn't format an unlocked cell on a protected sheet via Format|Cells.)

Rick wrote:

Hi all,

I have used the "EnableSelection = xlUnlockedCells" to Protect some worksheets, but this allows the
cells to be reformatted. What I want are cells that can only have their values changed and not
allow them to have their formatting altered...this includes copying or cutting and pasting.

How can this be accomplished using VBA code in a code segment like:

With Worksheets(Indx)
.Unprotect

' do my stuff here...

.EnableSelection = xlUnlockedCells
.Protect
End With

TIA,

Rick

--

Dave Peterson

Dave Peterson wrote:
Maybe you could just intercept the change. This worked in light testing.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myFormulas As Variant

On Error GoTo errHandler:

myFormulas = Target.Formula
With Application
.EnableEvents = False
.Undo
End With
Target.Formula = myFormulas

errHandler:
Application.EnableEvents = True

End Sub

rightclick on the worksheet tab that should have this behavior. Select View
code and paste this in.

(But I couldn't format an unlocked cell on a protected sheet via Format|Cells.)

Rick wrote:

Hi all,

I have used the "EnableSelection = xlUnlockedCells" to Protect some worksheets, but this allows the
cells to be reformatted. What I want are cells that can only have their values changed and not
allow them to have their formatting altered...this includes copying or cutting and pasting.

How can this be accomplished using VBA code in a code segment like:

With Worksheets(Indx)
.Unprotect

' do my stuff here...

.EnableSelection = xlUnlockedCells
.Protect
End With

TIA,

Rick


Thanks Dave,

The problem I had was that a cut and paste would paste the formatting too...I just want to allow
changing the cells value.

Rick

What happened when you tried it?



Rick wrote:

Dave Peterson wrote:
Maybe you could just intercept the change. This worked in light testing.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myFormulas As Variant

On Error GoTo errHandler:

myFormulas = Target.Formula
With Application
.EnableEvents = False
.Undo
End With
Target.Formula = myFormulas

errHandler:
Application.EnableEvents = True

End Sub

rightclick on the worksheet tab that should have this behavior. Select View
code and paste this in.

(But I couldn't format an unlocked cell on a protected sheet via Format|Cells.)

Rick wrote:

Hi all,

I have used the "EnableSelection = xlUnlockedCells" to Protect some worksheets, but this allows the
cells to be reformatted. What I want are cells that can only have their values changed and not
allow them to have their formatting altered...this includes copying or cutting and pasting.

How can this be accomplished using VBA code in a code segment like:

With Worksheets(Indx)
.Unprotect

' do my stuff here...

.EnableSelection = xlUnlockedCells
.Protect
End With

TIA,

Rick


Thanks Dave,

The problem I had was that a cut and paste would paste the formatting too...I just want to allow
changing the cells value.

Rick

--

Dave Peterson

That question Simms familiar.

Dave Peterson wrote:
What happened when you tried it?

I just hope it works as well as George's solutions!

Debra Dalgleish wrote:

That question Simms familiar.

Dave Peterson wrote:
What happened when you tried it?

--

Dave Peterson

Dave Peterson wrote:
What happened when you tried it?



Rick wrote:

Dave Peterson wrote:

Maybe you could just intercept the change. This worked in light testing.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myFormulas As Variant

On Error GoTo errHandler:

myFormulas = Target.Formula
With Application
.EnableEvents = False
.Undo
End With
Target.Formula = myFormulas

errHandler:
Application.EnableEvents = True

End Sub

rightclick on the worksheet tab that should have this behavior. Select View
code and paste this in.

(But I couldn't format an unlocked cell on a protected sheet via Format|Cells.)

Rick wrote:


Hi all,

I have used the "EnableSelection = xlUnlockedCells" to Protect some worksheets, but this allows the
cells to be reformatted. What I want are cells that can only have their values changed and not
allow them to have their formatting altered...this includes copying or cutting and pasting.

How can this be accomplished using VBA code in a code segment like:

With Worksheets(Indx)
.Unprotect

' do my stuff here...

.EnableSelection = xlUnlockedCells
.Protect
End With

TIA,

Rick


Thanks Dave,

The problem I had was that a cut and paste would paste the formatting too...I just want to allow
changing the cells value.

Rick


Dave,

The code you gave me allowed a value to be pasted into a cell along with changing the "pattern"
format to match the origin. There are no formulas in the cells, they simply provide values for some
other formulas. The pattern color is important as it is used to separate columns

Rick B

Are you sure you put it in the correct spot--under that worksheet.

rightclick on the worksheet tab that should have this behavior. Select View
code and paste this in.



Rick wrote:

Dave Peterson wrote:
What happened when you tried it?



Rick wrote:

Dave Peterson wrote:

Maybe you could just intercept the change. This worked in light testing.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myFormulas As Variant

On Error GoTo errHandler:

myFormulas = Target.Formula
With Application
.EnableEvents = False
.Undo
End With
Target.Formula = myFormulas

errHandler:
Application.EnableEvents = True

End Sub

rightclick on the worksheet tab that should have this behavior. Select View
code and paste this in.

(But I couldn't format an unlocked cell on a protected sheet via Format|Cells.)

Rick wrote:


Hi all,

I have used the "EnableSelection = xlUnlockedCells" to Protect some worksheets, but this allows the
cells to be reformatted. What I want are cells that can only have their values changed and not
allow them to have their formatting altered...this includes copying or cutting and pasting.

How can this be accomplished using VBA code in a code segment like:

With Worksheets(Indx)
.Unprotect

' do my stuff here...

.EnableSelection = xlUnlockedCells
.Protect
End With

TIA,

Rick


Thanks Dave,

The problem I had was that a cut and paste would paste the formatting too...I just want to allow
changing the cells value.

Rick


Dave,

The code you gave me allowed a value to be pasted into a cell along with changing the "pattern"
format to match the origin. There are no formulas in the cells, they simply provide values for some
other formulas. The pattern color is important as it is used to separate columns

Rick B

--

Dave Peterson