Mar 11 2021 05:40 AM
Hi there,
I want to put some restrictions on a cell but I'm unsure how to do it.
If the word "Yes" has been typed into cell B2, i want to allow the user to input any numerical value in cell B5. However if the word "No" has been typed into cell B2, I want to prevent the user from entering any value to cell B5 and also set the value of B5 to zero.
I'm struggling a little to work out how to merge the commands together so if anyone has any ideas they can offer, they would be greatly appreciated.
Many thanks :)
Mar 11 2021 05:56 AM
This requires several steps.
1) Set up data validation for cell B5 to allow whole numbers or decimal numbers; specify a wide enough range.
2) Select all cells that the user should be able to edit, including B2 and B5.
Press Ctrl+1 to activate the Format Cells dialog.
Activate the Protection tab.
Clear the Locked check box.
Click OK.
3) On the Review tab of the ribbon, click Protect Sheet.
Use the check boxes to specify what users will be allowed to do. At the very least, leave the check box "Select unlocked cells" ticked.
If you wish, specify a password that will be needed to unprotect the sheet. Do not forget it!
Click OK. If you entered a password, you will have to re-enter it as confirmation.
In the step below, I will use "secret" as password.
4) Right-click the sheet tab and select "View Code" from the context menu.
Copy the following code into the worksheet module:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("B2"), Target) Is Nothing Then
Me.Unprotect Password:="secret"
Application.EnableEvents = False
Select Case Range("B2").Value
Case "Yes"
Range("B5").Locked = False
Case Else
Range("B5").Locked = True
Range("B5").Value = 0
End Select
Application.EnableEvents = True
Me.Protect Password:="secret"
End If
End Sub
5) Switch back to Excel.
Save the workbook as a macro-enabled workbook (.xlsm).
Make sure that users allow macros when they open it.
Mar 12 2021 05:53 AM
@Hans Vogelaar Wow amazing, I'll give that a try. Thanks so much for your quick reply :)