Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 8
Default Prevent formatting Cells with Protection

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
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,824
Default Prevent formatting Cells with Protection

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

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 8
Default Prevent formatting Cells with Protection

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

  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,824
Default Prevent formatting Cells with Protection

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

  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default Prevent formatting Cells with Protection

That question Simms familiar.

Dave Peterson wrote:
What happened when you tried it?




  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,824
Default Prevent formatting Cells with Protection

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

  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 8
Default Prevent formatting Cells with Protection

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
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,824
Default Prevent formatting Cells with Protection

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

  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 8
Default Prevent formatting Cells with Protection

Dave Peterson wrote:
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?



I guess something happened to my last reply...hmmm?

Well Dave, the code did nothing. The values and the formatting changed when I used a copy and paste.
Is there a way to trap a paste event and turn it into a PasteSpecial "Values Only"?

Rick
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,824
Default Prevent formatting Cells with Protection

I thought that the code I provided did that.

I just tried it again and it worked (again) for me.

try adding a message box in the code:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myFormulas As Variant

MsgBox "I made it here!"

On Error GoTo errHandler:

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

errHandler:
Application.EnableEvents = True

End Sub

When you make a change, do you get the message box?

If yes, then I don't have another suggestion.

If no, then you may have put the code in the wrong location (it belongs under
the worksheet).

Or you may have macros disabled or you may have disabled event processing.

I'm still guessing the wrong location.

The code remembers the formulas (myformulas = target.formula). Then it does an
undo so it's back to its previous state. Then it assigns the formulas--just the
formulas--no formatting.



Rick wrote:

Dave Peterson wrote:
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?



I guess something happened to my last reply...hmmm?

Well Dave, the code did nothing. The values and the formatting changed when I used a copy and paste.
Is there a way to trap a paste event and turn it into a PasteSpecial "Values Only"?

Rick


--

Dave Peterson

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can you prevent Copy-Paste from changing formatting without macros DCramlet Excel Discussion (Misc queries) 6 May 30th 14 07:18 AM
Prevent paste from re-formatting cell BMac Excel Discussion (Misc queries) 4 January 14th 09 10:42 PM
Is there a way to prevent Excel from automatically formatting data Jason Excel Discussion (Misc queries) 2 November 26th 08 02:48 AM
Prevent Formatting of a Cell when it is loaded Lizette Koehler Excel Discussion (Misc queries) 7 June 20th 07 12:38 AM
Prevent unhide of rows/columns w/out worksheet protection? annonymous Excel Discussion (Misc queries) 2 March 10th 05 08:23 PM


All times are GMT +1. The time now is 05:26 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"