How can I project sheet formulas whist having a save button on other cells to project them?

[mattbarb]

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica; color: #000000}p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica; color: #000000; min-height: 14.0px}</style>



I have a worksheet with columns A1-A10 with a formala which calculate B1-B10 values which are inputted by a user.

• I would like to lock A1-A10 so that the user cannot change the formula (project from editing).
• I would also like the user to be able to enter values in B1-B10 and when done save the numbers (projected from editing) so they cannot later be changed.
• I don’t want to lock the entire book from editing as I have cell that the user will need to update regularly.

Im looking for a VBA script that would let me save the entered values in B1-B10 by pressing a button?
Ive tried everything and just cannot seem to get it to work.






Any help would be really appreciated.


Thanks,
Matt

 

[Yongle]

One way ...

Instead of protecting B1:B10, prevent user selecting those cells
Example code below - amend to suit your own needs

Cell A11 holds value "Lock" when cells are being safegearded
user is prevented from selecting A11
user is prevented from selecting A1:A10
user is prevented from selecting B1:B10 if value in A11 = "Lock"

Place code in SHEET module (will not work if placed anywhere else )
right-click sheet tab \ View Code \ paste VBA into code window \ back to Excel with {ALT}{F11}

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim A As Range, B As Range, X As Range
    Set A = Range("A1:A10")
    Set B = Range("B1:B10")
    Set X = Range("[COLOR=#ff0000]A11[/COLOR]")
    If Not Intersect(Target, A) Is Nothing Then Range("C1").Select
    If Not Intersect(Target, X) Is Nothing Then Range("C1").Select
    
    If Not Intersect(Target, B) Is Nothing Then
        If Range("[COLOR=#ff0000]A11[/COLOR]") = "Lock" Then Range("C1").Select
    End If
End Sub

Button code (protect)

    If MsgBox("Protect values ?", vbYesNo, "User Conformation") = vbYes Then
        Range("[COLOR=#ff0000]A11[/COLOR]") = "Lock"
    End If

Code to unprotect (password abc required to allow A11 to be edited)

    If InputBox("Password", "Enter Password", "?") = "abc" Then
        If MsgBox("Expose values ?", vbYesNo, "User Conformation") = vbYes Then Range("[COLOR=#ff0000]A11[/COLOR]").ClearContents     
    End If