Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prevent formatting Cells with Protection
That question Simms familiar.
Dave Peterson wrote: What happened when you tried it? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prevent formatting Cells with Protection
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can you prevent Copy-Paste from changing formatting without macros | Excel Discussion (Misc queries) | |||
Prevent paste from re-formatting cell | Excel Discussion (Misc queries) | |||
Is there a way to prevent Excel from automatically formatting data | Excel Discussion (Misc queries) | |||
Prevent Formatting of a Cell when it is loaded | Excel Discussion (Misc queries) | |||
Prevent unhide of rows/columns w/out worksheet protection? | Excel Discussion (Misc queries) |