How to Lock Cells/Content While Using VBA?

JPhotonics · Nov 23, 2016

I have a worksheet I'd like to add protection to. When I add protection within Excel, it seems to break my VBA functionality. Do I need to add protection within VBA rather than Excel? If so, does it matter where within my current VBA code this new "protection code" is placed?

I want to be able to select rows/columns/cells which cannot be re-formatted or changed by the user. I have attached my file to show the other VBA code I am using with it.

PCosta87 · Nov 23, 2016

Hi @JPhotonics

Before protecting the sheet select which cells will be protected and which will not. You can do this by pressing Ctrl+1 and checking/unchecking "Protected" in the protection tab.
When you protect the sheet afterwards, just select what options will be available.

Hope this helps

JPhotonics · Dec 5, 2016

PCosta87 said:
Hi @JPhotonics

Before protecting the sheet select which cells will be protected and which will not. You can do this by pressing Ctrl+1 and checking/unchecking "Protected" in the protection tab.
When you protect the sheet afterwards, just select what options will be available.

Hope this helps

Sorry for the delayed response, I have been working on other projects and this has been pushed onto the back-burner. This week I have some time to get back into it though.

When I do it this way, it seems to cause me errors. I started off by clearing "Locked" from all of my cells and then only selecting "locked" for cells I actually want to lock. However, it seems to cause an error still. See my screenshot below

JPhotonics · Dec 5, 2016

I have also tried to lock the whole sheet and unlock certain cells through VBA. It then appears to break at the point where I want to validate the Task Completion before allowing "Phase Status" to be chosen, at which point it does not allow me to choose the Phase Status.

Also, if I keep my Auto-fitting for the "Comments" rows/cells at the top of my VBA, that's where the macro seems to fail instead of the Validation Input as shown below.

I want to lock the cells of my spreadsheet showing the "Tasks" so they are not deleted/changed. I also want to lock the formula used in the "Overall Task Status" Cell, but the result in the cell needs to be able to change based on the selection of Status below.

See screenshots below:

Here is my full code:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False

If Not Intersect(Target, Range("D3")) Is Nothing Then
    If Target.Resize(1, 1).Value = "" Then
        Target.Resize(1, 1).Value = "< Project Name >"
        Target.Interior.ColorIndex = 40
    End If
End If

If Not Intersect(Target, Range("D4")) Is Nothing Then
    If Target.Resize(1, 1).Value = "" Then
        Target.Resize(1, 1).Value = "< Project Manager >"
        Target.Interior.ColorIndex = 40
    End If
End If

If Not Intersect(Target, Range("D5")) Is Nothing Then
        If Target.Resize(1, 1).Value = "" Then
        Target.Resize(1, 1).Value = "< Primary Contact Email >"
        Target.Interior.ColorIndex = 40
        Range("D5").Hyperlinks.Delete
        Range("D5").Font.Underline = False
        Range("D5").Font.Color = 0
    End If
End If

If Not Intersect(Target, Range("D6")) Is Nothing Then
    If Target.Resize(1, 1).Value = "" Then
        Target.Resize(1, 1).Value = "< Primary Contact Phone >"
        Target.Interior.ColorIndex = 40
    End If
End If

If Not Intersect(Target, Range("I8")) Is Nothing Then
    If Target.Resize(1, 1).Value = "" Then
        Target.Resize(1, 1).Value = "Pending Review"
        Target.Interior.ColorIndex = 25
    End If
End If
If Not Intersect(Target, Range("I22")) Is Nothing Then
    If Target.Resize(1, 1).Value = "" Then
        Target.Resize(1, 1).Value = "Pending Review"
        Target.Interior.ColorIndex = 25
    End If
End If
If Not Intersect(Target, Range("I32")) Is Nothing Then
    If Target.Resize(1, 1).Value = "" Then
        Target.Resize(1, 1).Value = "Pending Review"
        Target.Interior.ColorIndex = 25
    End If
End If

myList$ = Range("Approval_List").Address
Application.EnableEvents = False
With Range("I8")
   If Range("D9") = "Completed" Then
    .Validation.Modify Type:=xlValidateList, Formula1:="=" & myList
   Else
    .Validation.Modify Type:=xlValidateInputOnly
   End If
End With
Application.EnableEvents = True

myList$ = Range("Approval_List").Address
Application.EnableEvents = False
With Range("I22")
   If Range("D23") = "Completed" Then
    .Validation.Modify Type:=xlValidateList, Formula1:="=" & myList
   Else
    .Validation.Modify Type:=xlValidateInputOnly
   End If
End With
Application.EnableEvents = True

myList$ = Range("Approval_List").Address
Application.EnableEvents = False
With Range("I32")
   If Range("D33") = "Completed" Then
    .Validation.Modify Type:=xlValidateList, Formula1:="=" & myList
   Else
    .Validation.Modify Type:=xlValidateInputOnly
   End If
End With
Application.EnableEvents = True

If Target.Address <> "$I$9" And Target.Address <> "$I$23" And Target.Address <> "$I$33" Then
If Range("I8").Text = "Approved" Or Range("I8").Text = "Approved w/ Comments" Then
If Range("$I$9").Text = "" Then
Range("I9").FormulaR1C1 = "=NOW()"
Range("I9").Copy
Range("I9").PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
End If
Else
Range("I9").FormulaR1C1 = ""
End If

If Range("I22").Text = "Approved" Or Range("I22").Text = "Approved w/ Comments" Then
If Range("$I$23").Text = "" Then
Range("I23").FormulaR1C1 = "=NOW()"
Range("I23").Copy
Range("I23").PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
End If
Else
Range("I23").FormulaR1C1 = ""
End If

If Range("I32").Text = "Approved" Or Range("I32").Text = "Approved w/ Comments" Then
If Range("$I$33").Text = "" Then
Range("I33").FormulaR1C1 = "=NOW()"
Range("I33").Copy
Range("I33").PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
End If
Else
Range("I33").FormulaR1C1 = ""
End If

End If
Application.ScreenUpdating = True

Call Worksheets("Stage Gate Checklist").Protect(UserInterfaceOnly:=True)
Worksheets("Stage Gate Checklist").Range("E12:E20").Locked = False

Range("B10:J10").Rows.AutoFit
Range("B24:J24").Rows.AutoFit
Range("B34:J34").Rows.AutoFit

End Sub

PCosta87 · Dec 6, 2016

Hi,

VBA will likely throw an error when trying to manipulate locked cells.
Is it possible some of the cells that VBA needs to manipulate are being left locked?
For instance, with "I8" you are doing ".Validation.Modify"... is I8 protected?

From error code "1004" I would say it has something to do with VBA trying to change protected cell but can't say for sure without some debugging.

If you can't figure out the root cause of the error, please upload file with latest changes (protection included, preferably without password

) and I will gladly try to help in debugging.

JPhotonics · Dec 6, 2016

PCosta87 said:
Hi,

VBA will likely throw an error when trying to manipulate locked cells.
Is it possible some of the cells that VBA needs to manipulate are being left locked?
For instance, with "I8" you are doing ".Validation.Modify"... is I8 protected?

From error code "1004" I would say it has something to do with VBA trying to change protected cell but can't say for sure without some debugging.

If you can't figure out the root cause of the error, please upload file with latest changes (protection included, preferably without password ) and I will gladly try to help in debugging.

I'm not really sure to be honest. I tried to completely unlock all of the cells of the sheet first by highlighting all and going to the CTRL+1 menu where you can lock/hide cells, but that did not seem to change anything. It still seems to throw an error code.

Currently there is no protection on the sheet and no password protection. Here is what I am trying to lock for cells - basically Cell/Task names:

B2:C2
B3:C3
B4:C4
B5:C5
B6:C6

B8:F8, G8:H8
B9:C9, G9:H9
B10:C10
B11:B20, E11, G11:G19, J11

B22:F22, G22:H22
B23:C23, G23:H23
B24:C24
B25:B30, E25, G25:G30, J25

B32:F32, G32:H32
B33:C33, G33:H33
B34:C34
B35:B39, E35, G35:G39, J35

So, I want to lock most cells, except for the following:

Project Name, Project Manager, Primary Contact Email, Primary Contact Phone
Phase Status - user selects whether they approve
Phase Approval Date - creates a timestamp on Status selection
Overall Task Status - displays when Tasks are all complete
Comments - user can freely type comments
Status - user needs to select a status for each Task

I have attached my current workbook, thank you for your input it is much appreciated.

PCosta87 · Dec 6, 2016

Hi,

Here you go... should be working now.

I added "activesheet.unprotect" at the beginning of the code and "activesheet.protect" at the end.
I left the cells you posted above unprotected and protected everything else...

The problem was that you can't add validation (or change its type in this case) when sheet is protected.

Let me know if you have any further questions

JPhotonics · Dec 8, 2016

PCosta87 said:
Hi,

Here you go... should be working now.

I added "activesheet.unprotect" at the beginning of the code and "activesheet.protect" at the end.
I left the cells you posted above unprotected and protected everything else...

The problem was that you can't add validation (or change its type in this case) when sheet is protected.

Let me know if you have any further questions

Thank you for your help, but I do not believe this fixed my issues.

Now when I make a selection for all of my Tasks, the Overall Task Status correctly displays "Complete", however, I should then be able to select my Phase Status from that cell's drop-down menu. When I click into that cell, there is no clickable drop-down or way to select my Phase Status. (screenshot below)

Also, it still allows me to go in and delete the cell which contains "Completed" for the Overall Task Status and when that happens, the formula is lost so if I go and select an option for my Task Statuses, it still will not display "Completed". (screenshot below)

PCosta87 · Dec 9, 2016

Hi,

So, for the overall task status I just protected it as well (since it will not allow user input)... Now when protected it will not allow those cells to be selected.

As for the dropdown... it is working here, not really sure why it doesn't on your end

Please see attached

JPhotonics · Dec 12, 2016

PCosta87 said:
Hi,

So, for the overall task status I just protected it as well (since it will not allow user input)... Now when protected it will not allow those cells to be selected.

As for the dropdown... it is working here, not really sure why it doesn't on your end View attachment 36694

Please see attached

Very odd. So I downloaded the updated sheet you attached and ran it... the Timestamp worked. I didn't save it, re-opened it and now it is not displaying the Timestamp for the Phase Status.

The "Design Phase" and "Production Phase" Phase Status drop-downs are not working. So, I closed Excel down and re-opened the file and the drop-downs appear to work as well as all of the Timestamping. But, now I have noticed my functionality in my top segment is not working where I want the cell to re-format if the contents are cleared.

Also, if I attempt to clear out the answer from Overall Task Status or Phase Status by pressing the "Delete" key, the formula and functionality of the cell is removed. Is there a way to allow the displayed text to be cleared, but for the functionality and formulas to remain?

See the screenshots below:

PCosta87 · Dec 14, 2016

Hi @JPhotonics

The issue you have is that the "if" condition for the Phase status doesn't take into account the actual state of the cell... it only looks for "Complete" on the overall task status and applies the validation modification.
You can add

Code:

On error resume next

at the start of the code and it should take care of that.

About deleting the overall task status, since it is a formula, you can either have VBA add the formula again if it gets deleted, or add code to input "Completed" as text in the cell when the conditions are met and remove it when they aren't

In any case, I would not allow for the user to delete this status since it is a formula... In my opinion it should be protected and change only based on the formula you have in place.

Please let me know if it works

Thanks

JPhotonics · Dec 15, 2016

PCosta87 said:
Hi @JPhotonics

The issue you have is that the "if" condition for the Phase status doesn't take into account the actual state of the cell... it only looks for "Complete" on the overall task status and applies the validation modification.
You can add

Code:

On error resume next

at the start of the code and it should take care of that.

About deleting the overall task status, since it is a formula, you can either have VBA add the formula again if it gets deleted, or add code to input "Completed" as text in the cell when the conditions are met and remove it when they aren't

In any case, I would not allow for the user to delete this status since it is a formula... In my opinion it should be protected and change only based on the formula you have in place.

Please let me know if it works

Thanks

Ah, I think I see what you are saying with the "IF" condition for Phase Status.

What would be the best method? Should I have VBA put the formula back in if the cell contents are deleted? I assume I should use something similar to what I used to re-format the columns like "Project Name" or "Project Manager", correct? Here's what I have in there currently:

Code:

If Not Intersect(Target, Range("D4")) Is Nothing Then
    If Target.Resize(1, 1).Value = "" Then
        Target.Resize(1, 1).Value = "< Project Manager >"
        Target.Interior.ColorIndex = 40
    End If
End If

How would I have it re-insert the proper formula?

Now I just re-opened my worksheet and I don't even have the ability to select the "Overall Task Status" cells to delete those contents. But, I can select and delete the "Phase Status" cell which breaks the VBA (screenshot below & file attached):

I agree, I do not want to let the user delete this cell/formula, but I am not sure how to allow them to select an option from the drop-down menu without allowing them to delete or clear out the contents of the cell.

Thank you for all of the help!

PCosta87 · Dec 16, 2016

Hi,

If the file you uploaded last has the last changes, then I see you haven't added

Code:

On Error Resume Next

That would prevent the code from stopping, and for what I can see solves most of the problems, like not being able to select a phase status from the dropdown menu after deleting.

As for deleting the "Overall task status", since that doesn't need user input, it could be locked (which is why you couldn't select it... I posted earlier:

for the overall task status I just protected it as well (since it will not allow user input)... Now when protected it will not allow those cells to be selected

This way you shouldn't need to have any extra VBA code to handle the deletion of these cells.

Please refer to attachment

JPhotonics · Dec 19, 2016

PCosta87 said:
Hi,

If the file you uploaded last has the last changes, then I see you haven't added

Code:

On Error Resume Next

That would prevent the code from stopping, and for what I can see solves most of the problems, like not being able to select a phase status from the dropdown menu after deleting.

As for deleting the "Overall task status", since that doesn't need user input, it could be locked (which is why you couldn't select it... I posted earlier:
This way you shouldn't need to have any extra VBA code to handle the deletion of these cells.

Please refer to attachment

I think you have fixed it, thank you sir! Everything appears to be working properly and I have not yet been able to break it. Thank you very much for all of your help.

Now I'll try and read through the VBA and figure out why it is that way so hopefully I'll learn for the next time.

What is odd to me is the part where it locks E12:E20... shouldn't more than that be locked in the code since there are far more cells than just E12:E20 which are being blocked/locked from being able to be deleted? Here's the code:

Code:

Call Worksheets("Stage Gate Checklist").Protect(UserInterfaceOnly:=True)
Worksheets("Stage Gate Checklist").Range("E12:E20").Locked = False
ActiveSheet.Protect

PCosta87 · Jan 3, 2017

JPhotonics said:
I think you have fixed it, thank you sir! Everything appears to be working properly and I have not yet been able to break it. Thank you very much for all of your help.

Now I'll try and read through the VBA and figure out why it is that way so hopefully I'll learn for the next time.

What is odd to me is the part where it locks E12:E20... shouldn't more than that be locked in the code since there are far more cells than just E12:E20 which are being blocked/locked from being able to be deleted? Here's the code:

Code:

Call Worksheets("Stage Gate Checklist").Protect(UserInterfaceOnly:=True) Worksheets("Stage Gate Checklist").Range("E12:E20").Locked = False ActiveSheet.Protect

Hi,

First things first... happy new year

I'm glad I could help

As for your question, cells can be protected in excel (which is the case)... this protection/lock will take effect after protecting the sheet which we did in VBA. This way you can manually select which cells to protect without having to modify the code.

PS: Sorry for the late response... I was away on vacation and didn't check the forum

How to Lock Cells/Content While Using VBA?

JPhotonics

Member

Attachments

PCosta87

Well-Known Member

JPhotonics

Member

JPhotonics

Member

PCosta87

Well-Known Member

JPhotonics

Member

Attachments

PCosta87

Well-Known Member

Attachments

JPhotonics

Member

PCosta87

Well-Known Member

Attachments

JPhotonics

Member

PCosta87

Well-Known Member

JPhotonics

Member

Attachments

PCosta87

Well-Known Member

Attachments

JPhotonics

Member

PCosta87

Well-Known Member