Unlocking cells and modifying cell values

Folks,
This is a snippet of code that is not working:
 
Private Sub cmdCkAnserAverageCon_Click()
   If Range("D7").Text <> "13.23" Then
   Cells.Select.Range("E7").Locked = False
   Cells.Select.Range("E7").Font = White
   Cells.Select.Range("E7").Background = Red
   Cells.Select.Range("E7").Value = "Error"
   CheckFormulaFunction
    Exit Sub
    End If
        
    FormulasOK
    Cells.Select.Range("E7:E15").Locked = True
    Cells.Select.Range("E7:E15") = Clear

End Sub

Open in new window


Here's what I'd like to do. If the value in D7 does not agree with what it should be the formula is incorrect. The worksheet is protected except where the user enters their formulas (this is for training purposes). If it does not agree then in the above example I like to unprotect E7, change its background to red, the font to white and its value to be "Error".
If all the formulas are OK then make sure the contents of cells from E7:E15 are cleared and that all cells from E7:E15 are locked
Frank FreeseAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saqib Husain, SyedEngineerCommented:
Here are some corrections

Private Sub cmdCkAnserAverageCon_Click()
   If Range("D7").Text <> "13.23" Then
   Range("E7").Locked = False
   Range("E7").Font.Color = vbWhite
   Range("E7").Interior.Color = vbRed
   Range("E7").Value = "Error"
   CheckFormulaFunction
    Exit Sub
    End If
       
    FormulasOK
    Range("E7:E15").Locked = True
    Range("E7:E15") = Clear

End Sub



******edited******
0
byundtMechanical EngineerCommented:
You might try protecting the worksheet with UserInterfaceOnly being True. The code snippet below shows it as the first statement in your macro (for testing), but the better place to do it is when the workbook opens as shown in the second snippet.
Private Sub cmdCkAnserAverageCon_Click()
ActiveSheet.Protect UserInterfaceonly:=True
If Range("D7").Text <> "13.23" Then
    Range("E7").Locked = False
    Range("E7").Font.Color = vbWhite
    Range("E7").Interior.Color = vbRed
    Range("E7").Value = "Error"
    CheckFormulaFunction
    Exit Sub
End If
        
FormulasOK
Range("E7:E15").Locked = True
Range("E7:E15").Clear

End Sub

Open in new window


'This sub must be installed in ThisWorkbook code pane. It won't work at all if installed anywhere else!
Private Sub Workbook_Open()
Worksheets("Sheet1").Protect UserInterfaceOnly:=True
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
byundtMechanical EngineerCommented:
I see that ssaqibh caught some of the same bugs I did, but posted them first. My apologies.
0
Frank FreeseAuthor Commented:
Everything above this looks good.
Here's I would like to restore to cells E7:E15 to their defaults. This did not work
 Range("E7:E15").Locked = True
 Range("E7:E15") = Clear

I changed to this and it looks to be working.  
   Range("E7:E15").Font.Color = vbBlack
   Range("E7:E15").Interior.Color = vbWhite
   Range("E7:E15").Value = " "
   Range("E7:E15").Locked = True
0
Frank FreeseAuthor Commented:
Thanks folks - I appreciate your comments and help very much
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.