-1

My excel tool allow me to calculate prices. There is this dropdown list cell that indicates the product, and another dropdown list cell to indicates the city where is should be delivered, but what I would like to do is to freeze country cell(set it to one value) if a certain value is selected for the product.

Working on Excel 2019

BrokenStrings
  • 1
  • 2

1 Answers1

0

You're looking for Worksheet.Protect and Range.Locked.

Once the user has entered a value for product and you want to lock the country cells, you will need to do something like the following:

Activesheet.Unprotect
Range("Everything Except Country").Locked = False
Range("Country").Locked = True
Activesheet.Protect

Once you protect the sheet, the default for all cells is Locked, so you only need to define the areas where it should still be editable. If you want to continue locking additional cells as the user enters more data, you will need to unprotect the sheet, redefine the locked ranges and then re-protect the sheet.

I would suggest using the Worksheet_Change event with something like the following:

Private Sub Worksheet_Change(ByVal Target As Range)

if Target.Address = Range("Product").Address then
   Activesheet.Unprotect
   Range("Everything Except Country").Locked = False
   Range("Country").Locked = True
   Activesheet.Protect
end if

End Sub

You may also want to add an event or button where the sheet can reset to all cells being unprotected.

Toddleson
  • 4,321
  • 1
  • 6
  • 26
  • ```I don't really know how to apply it to my data. If I have a dropdown list of products {Rings,Earrings,Necklaces} and another dropdown list of countries {France, Germany, Spain, Italy}, how can I do when in other cells C18 for example when selecting Rings the country cell C20 can only be France ?``` – BrokenStrings Nov 12 '20 at 20:15
  • The cells C18 and C20 are not the initial dropdown cells, they are separate in order to calculate final price – BrokenStrings Nov 12 '20 at 20:17
  • @BrokenStrings I am not sure what you mean, but if you are looking to freeze cells based on the inputs of other cells then my solution is what you are looking for. You would just need to dynamically determine the ranges to protect and unprotect based on what the user has input. I would suggest keeping the protected range and unprotected range as a module-level variable so that it can be saved between events and easily modified. – Toddleson Nov 13 '20 at 21:48