PDA

View Full Version : Macro to Protect formula cells



ksquirt
01-20-2009, 09:50 AM
I am pretty new to writing macros. I have seen numerous codes to do part of this, but they don't work. I need to (I think) unlock all the cells without a formula and then protect the sheet without a password. I have numerous sheets in each workbook. Please help!

ksquirt
01-20-2009, 10:18 AM
I finally found one that worked!! Thanks for viewing!


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) Dim formula As Range
On Error Resume Next
Sh.Unprotect Password:="password"
With Selection
.Locked = False
.FormulaHidden = False
End With
If Target.Cells.Count = 1 Then
If Target.HasFormula Then
With Target
.Locked = True
.FormulaHidden = True
End With
Sh.Protect Password:="password", UserInterFaceOnly:=True
End If
ElseIf Target.Cells.Count > 1 Then
Set formula = Selection.SpecialCells(xlCellTypeFormulas)
If Not formula Is Nothing Then
With Selection.SpecialCells(xlCellTypeFormulas)
.Locked = True
.FormulaHidden = True
End With
Sh.Protect Password:="password", UserInterFaceOnly:=True
End If
End If
On Error GoTo 0
End Sub



Instructions: (I tried to post the link, but my post count is at 2...)

lucas
01-20-2009, 10:22 AM
Welcome to the forum ksquirt. Glad you figured this out and shared your solution. You can mark your thread solved using the thread tools at the top of the page.

ksquirt
01-20-2009, 10:30 AM
I did try to mark it solved, but now I have a problem with it. My macros won't run. It does have UserInterFaceOnly:=True, so I'm not sure what the issue is.

lucas
01-20-2009, 12:02 PM
It works if you put it in the thisworkbook module and move the dim statement down to the next line...dim formula as range on second line.

lucas
01-20-2009, 12:04 PM
I need to (I think) unlock all the cells without a formula and then protect the sheet without a password

you are using the password "password" in the code above....

ksquirt
01-20-2009, 12:10 PM
Yeah... I don't mind a generic password. I'd rather just not have one, so somebody could unprotect without one, but this is what I ended up with. I put in a return before the Dim statement and the macros still don't work. I just get an hourglass and have to Task Manager End Task to get out of it.

lucas
01-20-2009, 12:17 PM
try to change cell c1, it has a formula in it. go to a different sheet and add a formula and then try to change it.....

ksquirt
01-20-2009, 12:28 PM
Sorry, I sometimes have issues not being clear. All of my other macros are not working correctly. I attached my template, maybe that will help.

lucas
01-20-2009, 12:40 PM
You have now protected all sheets with the password "password" Macro's you run on those sheets that effect the cells with formula's will not run until you unprotect them....
Option Explicit
Sub yoursub()
Worksheets("Sheet1").Unprotect Password:="MyPassword"
'your macro goes here
Worksheets("Sheet1").Protect Password:="MyPassword", userinterfaceonly:=True
End Sub

ksquirt
01-20-2009, 12:50 PM
Does Sheet1 need to reference a particular sheet? Can I have it say workbook somehow instead? It doesn't work as written. :(

lucas
01-20-2009, 01:00 PM
Sheet1 is the tab name of the sheet that the macro operates on. If it is all sheets then you need to unprotect them one at a time and run the macro and move to the next.....

lucas
01-20-2009, 01:05 PM
You need to do a little homework on your own. Read through this thread where the poster is facing the same problem you are....trying to run macro's on protected sheets.


http://vbaexpress.com/forum/showthread.php?t=24743&page=2

ksquirt
01-21-2009, 01:19 PM
So, I've been doing lots more research and here's my Protect macro:

Sub Protect_All()
'Turn Off annoying screen flicker
Application.ScreenUpdating = False
For i = 1 To Sheets.Count

On Error Resume Next
Cells.Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Locked = False
Selection.FormulaHidden = False

If Target.Cells.Count = 1 Then
Cells.Select
Selection.SpecialCells(xlCellTypeFormulas, 23).Select
Selection.Locked = True
Selection.FormulaHidden = True
Sheets(i).Protect Password:="password", UserInterFaceOnly:=True

End If
Next i
On Error GoTo 0

ActiveWorkbook.Protect Password:="password"
Sheets("AU Summary").Select
Range("C2").Select
'Turn On annoying screen flicker
ActiveWindow.ScrollColumn = 1
Application.ScreenUpdating = True

End Sub

My current issue is that is does not allow the users to delete rows. So, I found this macro, but I can't seem to put it in the right location for it to work on all of the sheets. (I only used the red text and said rows 1-500.) Sometimes the formulas disappear altogether too. I'm not sure why.


Sub ProtectionOptions()

ActiveSheet.Unprotect 'Unlock row 1.

Rows("1:1").Locked = False
' Allow row 1 to be deleted on a protected worksheet.

If ActiveSheet.Protection.AllowDeletingRows = False Then
ActiveSheet.Protect AllowDeletingRows:=True
End If

MsgBox "Row 1 can be deleted on this protected worksheet."
End Sub

If you feel like helping, that'd be great. I'll keep playing on my own too.

lucas
01-21-2009, 02:34 PM
K, I'll be glad to look at this with you. You have been researching and learning. I'm in the middle of a few things so I may be in and out but expect me to have a few questions as to what you are trying to do.

First, you want the sheets protected but you want them to be able to delete rows? Is that correct?

I just had a minute so I noticed that your protect macro adds userinterface true but you don't let it delete rows there.......

maybe something like this but it's untested:
Sheets(i).Protect Password:="password", UserInterfaceOnly:=True, AllowDeletingRows:=True
look up protect in the visual basic editor's help file, it's different than the main help file.

lucas
01-21-2009, 02:53 PM
Ok, I gotta ask, are you trying to protect all sheets in the workbook for userinterface only and allow them to delete rows?

lucas
01-21-2009, 02:57 PM
This workbook is set up to lock cells with formula's and allow deleting of rows and it uses code to do it. Any sheet you add will get the same protection.

ksquirt
01-21-2009, 03:08 PM
All I REALLY wanted was the formulas protected. For the sheet to work to it's full extent they need to be able to delete rows and run numerous macros. I'm not completely sure what the userinterface is supposed to do. I will try to add the protectdelete rows as you mentioned to see if it works.

ksquirt
01-21-2009, 03:16 PM
I don't know what I'm doing wrong, but I can't access the code in your attachment. :(

lucas
01-21-2009, 03:40 PM
Look in the thisworkbook module, not a standard module or a sheet.

ksquirt
01-21-2009, 03:53 PM
That's part of where I got some of my code from. The problem seemed to be that it didn't lock all of the current sheets. I can retry it. Oh! and it didn't STAY unprotected when I ran the unprotect macro. I might have to try it as written again. Ok, I put your macro in This Workbook and ran the Unprotect macro, when I went to a cell with a formula it was protected/locked again.

Sub Unprotect_All()
For i = 1 To Sheets.Count
Sheets(i).Unprotect Password:="password"
Next i
ActiveWorkbook.Unprotect Password:="password"
End Sub


I am attaching the template prior to adding Protect_All and Unprotect_All to all of the macros.

lucas
01-21-2009, 03:57 PM
After you unprotect it the protection might not kick back in until you switch sheets. The protect code in thisworkbook is a sheetselectionchange procedure.....

ksquirt
01-21-2009, 03:59 PM
Oh... I don't think it will work like that then because of the other macros. They copy the All Data page and reformat it and other like things. I'm leaving in a minute and will try the other piece of code (protectrows) on a clean sheet. I kept running into missing formulas and I'm not sure what's going on there.

ksquirt
01-22-2009, 08:10 AM
Adding AllowDeletingRows:=True doesn't work. If any cell in the row is protected it can't delete it. Your macro definitely doesn't work in this spreadsheet. It re-protects upon change in ANY selection, not just worksheet. It also doesn't allow the other macros to run. I feel like I'm beating my head against the wall. Is what I'm trying to do even possible?

lucas
01-22-2009, 09:07 AM
Yeah, it's pretty hard to design a workbook with so many sheets and so many macros to run and also allow users to do some things but not others......

I would personally never allow any personal interaction by users with the sheets. You can then protect the sheets, run your macro and unprotect....do stuff.....reprotect. Move to next...

use full protection that way.