Please help to unlock

[hakemp001]

Here is the situation. I have a workbook that has two sheets, both need to be protected.

On worksheet 1 (name is: Calibration Check Tool) I have a calculate button that I press, which will show pass or fail on worksheet 1. The macro also opens worksheet 2 (name is: Calibration History), opens a prompt that asks the users name, then records all of worksheet 1 information.

As long as worksheet 2 is unprotected already, everything works. However, if I leave it protected, even though my macro tells it to unprotect, it won't. Which means I get an error code of:

"Run-time error '1004':

The cell or chart that you are trying to change is protected and therefore read-only.

To modify a protected cell or chart, first remove protection using the Unprotect Sheet command (Review tab, Changes group). You may be prompted for a password."

Below is the code that I am using: Please help!!

Sub ATP_Test()



Dim ws2 As Worksheet


'test to see if user has entered data
Select Case Range("c14").Value
    Case Is = ""
        MsgBox ("Enter data first")
        Exit Sub
    
End Select




ActiveSheet.Unprotect Password:="skfood2"




'Positive rod's average bounds
Select Case Range("f14").Value
    
    
    Case Is < 80
        Range("c19").Value = "Fail"
        Range("c19").Interior.ColorIndex = 3
    Case Is > 160
        Range("c19").Value = "Fail"
        Range("c19").Interior.ColorIndex = 3
    Case Is < 160
        Range("c19").Value = "Pass"
        Range("c19").Interior.ColorIndex = 4
    Case Is > 80
        Range("c19").Value = "Pass"
        Range("c19").Interior.ColorIndex = 4
  
        
        End Select
   


'Negative rod's average bounds
Select Case Range("f15").Value
    Case Is < 0
        Range("c20").Value = "Fail"
        Range("c20").Interior.ColorIndex = 3
    Case Is > 4
        Range("c20").Value = "Fail"
        Range("c20").Interior.ColorIndex = 3
    Case Is < 4
        Range("c20").Value = "Pass"
        Range("c20").Interior.ColorIndex = 4
    Case Is > 0
        Range("c20").Value = "Pass"
        Range("c20").Interior.ColorIndex = 4
End Select


'positive rod's percent variation from test 1 to average
Select Case Range("g14").Value
    Case Is > 20
        Range("d19").Value = "Fail"
        Range("d19").Interior.ColorIndex = 3
    Case Is < -20
        Range("d19").Value = "Fail"
        Range("d19").Interior.ColorIndex = 3
    Case Is < 20
        Range("d19").Value = "Pass"
        Range("d19").Interior.ColorIndex = 4
    Case Is > -20
        Range("d19").Value = "Pass"
        Range("d19").Interior.ColorIndex = 4
End Select
'positive rod's percent variation from test 2 to average
Select Case Range("h14").Value
    Case Is > 20
        Range("e19").Value = "Fail"
        Range("e19").Interior.ColorIndex = 3
    Case Is < -20
        Range("e19").Value = "Fail"
        Range("e19").Interior.ColorIndex = 3
    Case Is < 20
        Range("e19").Value = "Pass"
        Range("e19").Interior.ColorIndex = 4
    Case Is > -20
        Range("e19").Value = "Pass"
        Range("e19").Interior.ColorIndex = 4
End Select


'positive rod's percent variation from test 3 to average
Select Case Range("i14").Value
    Case Is > 20
        Range("f19").Value = "Fail"
        Range("f19").Interior.ColorIndex = 3
    Case Is < -20
        Range("f19").Value = "Fail"
        Range("f19").Interior.ColorIndex = 3
    Case Is < 20
        Range("f19").Value = "Pass"
        Range("f19").Interior.ColorIndex = 4
    Case Is > -20
        Range("f19").Value = "Pass"
        Range("f19").Interior.ColorIndex = 4
End Select




ActiveSheet.Protect Password:="skfood2"




For i = 3 To 6


    Select Case Cells(19, i).Interior.ColorIndex
        Case Is = 3
            Set ws2 = Worksheets("Calibration History")
            finalrow = ws2.Cells(Rows.Count, 2).End(xlUp).Row
            Call PopulateDatabase
            
            CorrectiveAction = InputBox("Fail: this machine is not calibrated.  Please enter corrective action (suggested course: Send in for repair)")
            ws2.Cells(finalrow + 1, 18).Value = CorrectiveAction
            
            QATechName = InputBox("Please enter your first and last name")
            ws2.Cells(finalrow + 1, 17).Value = QATechName
            MsgBox ("Thank you.  This Calibration Check was recorded.  See you again in two weeks!")
            Call printer
            Call email
            Call clear_atp_table
            ThisWorkbook.Save
            Exit Sub
    End Select
    
Next i






    Select Case Cells(20, 3).Interior.ColorIndex
        Case Is = 3
            Set ws2 = Worksheets("Calibration History")
            finalrow = ws2.Cells(Rows.Count, 2).End(xlUp).Row
            Call PopulateDatabase
            'enter corrective action
            CorrectiveAction = InputBox("Fail: this machine is not calibrated.  Please enter corrective action (suggested course: Send in for repair)")
            ws2.Cells(finalrow + 1, 18).Value = CorrectiveAction
            
            'enter qa tech name
            QATechName = InputBox("Please enter your first and last name")
            ws2.Cells(finalrow + 1, 17).Value = QATechName
            MsgBox ("Thank you.  This Calibration Check was recorded.  See you again in two weeks!")
            Worksheets("Calibration Check Tool").Activate
            Call printer
            Call email
            Call clear_atp_table
            
            ThisWorkbook.Save
            Exit Sub
    End Select


Set ws2 = Worksheets("Calibration History")
finalrow = ws2.Cells(Rows.Count, 2).End(xlUp).Row
Call PopulateDatabase
QATechName = InputBox("Please enter you first and last name")
ws2.Cells(finalrow + 1, 17).Value = QATechName
Worksheets("Calibration Check Tool").Activate


     Call printer
     Call email
     
' ActiveWorkbook.PrintOut Copies:=1, Collate:=True
Call clear_atp_table
ThisWorkbook.Save


End Sub

 

[paulxmw]

hi you need to amend the position of
ActiveSheet.Protect Password:="skfood2" as i think you are protecting the sheet before the macro has completed i would end your code with sheet name protect

or maybe replace the first protect sheet with

With ActiveSheet
.Protect Password:="skfood2", AllowCopyPast=True
.EnableSelection = xlUnlockedCells
End With

hope this points you in the right direction

Paul

 

[hakemp001]

I tried to move the position of my original
ActiveSheet.Protect Password:="skfood2" and Excel Could not Recognize the information.

I replaced the original with the code you gave me but gives me a syntax error on line:

.Protect Password:="skfood2", AllowCopyPast=True (It highlights the AllowCopyPast, and says Compile error: Expected: named parameter.

If I say that this is Excel 2010 instead of 2013, does this change things?

 

[mumps]

When this line of code is executed, which sheet is the active sheet?

ActiveSheet.Unprotect Password:="skfood2"

 

[hakemp001]

Worksheet 2 becomes "Active" (worksheet 2 = calibration history)

 

[mumps]

Try changing

ActiveSheet.Unprotect Password:="skfood2"

to

Sheets("calibration history").Unprotect Password:="skfood2"

 

[hakemp001]

Unfortunately, that still did not work. Still getting the:

Run-time error '1004':

 

[mumps]

It is always easier to help and test possible solutions if we could work with your actual file. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[hakemp001]

Great!! I will get to work on that right now. Thank you so much for all of your help so far