VBA lock cell

dmj120

Active Member
Joined
Jan 5, 2010
Messages
286
Office Version
  1. 365
  2. 2019
  3. 2010
I have the below macro to 'clear' data. I have a formula in D4 that rounds the value entered in D3.

How can I lock D4 to avoid anyone (even me - gottaAvoid****Happens) from editing the cell? The rounded number in D4 should only be copy-able - not editable.

VBA Code:
Sub Reset1()

Range("B2:B6").Value = Null  'clears search criteria

Range("A10:M" & Rows.Count).ClearContents  'clears resultant list of A10:M
Range("A9:M" & Rows.Count).Borders.LineStyle = xlNone 'clears previous "removeSpecials macro" borders
Range("E6").ClearContents  'clears high value
Range("E7").Value = "20"   'sets low value
Range("D3").Value = "verify 365 rnd"
Range("B5").Value = "fswp"  'sets default coverage


End Sub
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Not sure if its the best way but try highlighting all in the sheet, right click > format > protection, then unlock the whole sheet, then right click your D4 > format > protection > lock.

Then you just need to protect the sheet in the review tab and choose what you want to be able to do e.g. select the cell only, select and copy, full edit ability, etc.
 
Upvote 0
Not sure if its the best way but try highlighting all in the sheet, right click > format > protection, then unlock the whole sheet, then right click your D4 > format > protection > lock.

Then you just need to protect the sheet in the review tab and choose what you want to be able to do e.g. select the cell only, select and copy, full edit ability, etc.
Thanks, but I'm hoping to not use that feature.
 
Upvote 0
You can use a worksheet_change event to revert the value back if it is changed. For example:
VBA Code:
Private Sub worksheet_change(ByVal target As Range)
Application.EnableEvents = False
If Not Intersect(target, Range("D4")) Is Nothing Then
Application.Undo
End If
Application.EnableEvents = True
End Sub
 
Upvote 0
Solution

Forum statistics

Threads
1,213,561
Messages
6,114,316
Members
448,564
Latest member
ED38

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