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

mattbarb

Board Regular
Joined
Mar 22, 2018
Messages
54
Office Version
  1. 365
Platform
  1. MacOS
<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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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}
Code:
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)
Code:
    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)
Code:
    If InputBox("Password", "Enter Password", "?") = "abc" Then
        If MsgBox("Expose values ?", vbYesNo, "User Conformation") = vbYes Then Range("[COLOR=#ff0000]A11[/COLOR]").ClearContents     
    End If
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top