Block certain cells when you're on that sheet

Dwergkip

New Member
Joined
Sep 24, 2014
Messages
48
Hello everyone,

I want to block H16 and H20 when I'm on that sheet.
With a button I can insert the value, so it unlocks like that.
But once you can "edit" the page again. Those 2 cells have to be locked.
They also change when i'm in 3 other sheets. so the simple way is to just block them and change the protection of that sheet once the value changes in an other sheet.
But since this will take way too much time, I thought it might be possible to just lock them when you're on the sheet itself, since then it can't be changed from other sheets.

Example:
Code:
Sub Test1()
Worksheets("Sheet1").Unprotect Password:="xxx"
Range("F8").value = ""
Range("F9").value = ""
name = InputBox("Enter Name")
Range("F8").value = name
a = 1
Range("H20").value = "=F9"
Worksheets("Sheet1").Protect Password:="xxx"
End Sub

This is how I make sure the sheet is unlocked when I change values.
But since H16 and H20 will be changed in over 300 occasions It's a pain to put an unprotection and protection in every occassion.
So Basicly I want something like this, but then in VBA code

Activesheet = Sheet1
then Cells H16 and H20 Locked

Activesheet = Sheet 2 or anything else
then Cells H16 and H20 Unlocked

Is this possible?

Kind Regards,
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Forum statistics

Threads
1,215,345
Messages
6,124,408
Members
449,157
Latest member
mytux

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