Automatically change value in a cell while also allowing input

[SquareCare]

Hello!

I'm quite new to the more advanced tools in Ecxel and I have never used VBA before, but I have some limited experience with programming in general.
The worksheet is a calculator to help my colleagues calculate the prices of contracts before sending a quote to a customer. What I'm trying to acheive is as follows.

I have a drop-down list in cell B5 with "Standard price" and "Custom price".
I would like cells D9 and D10 to have a standard price of 750 and 950 respectively.
When B5 is set to "Standard price" I want cell D9 and D10 to be locked and show their standard values as mentioned above.
When B5 is set to "Custom price" I want the cells to be unlocked so that the user can enter a custom price.

If the user first sets cell B5 to "Custom price" and changes the values in D9 and D10, and then sets B5 to "Standard price" i want the value in D9 and D10 to reset to their
standard values again and be locked.

Bonus if possible: When "Standard price" is selected, it would be nice to have the text in D9 and D10 be slightly more gray than black to indicate the locked state.

Is this possible? If yes, could you please help me out?

Thank you for any help!
/Nick

 

[GSP75]

Hello Nick,

Yes, this is possible using VBA. Here's the code you can use:

First, right-click on the worksheet tab and click on "View Code". This will open up the VBA editor. Copy and paste the following code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$5" Then
If Target.Value = "Standard price" Then
Range("D9:D10").Locked = True
Range("D9").Value = 750
Range("D10").Value = 950
Range("D9:D10").Font.ColorIndex = 16 'Gray
ElseIf Target.Value = "Custom price" Then
Range("D9:D10").Locked = False
Range("D9:D10").Font.ColorIndex = xlAutomatic 'Default black
End If
End If
End Sub

This code will trigger whenever a change is made to the worksheet. It checks if the cell that was changed is B5, and if so, it checks the value of that cell. If it's "Standard price", it locks cells D9:D10, sets their values to 750 and 950 respectively, and changes their font color to gray. If it's "Custom price", it unlocks cells D9:D10 and changes their font color back to black.

To automatically reset the values in D9 and D10 when "Standard price" is selected, you can add the following code to the above:
If Target.Value = "Standard price" Then
Range("D9").Value = 750
Range("D10").Value = 950
End If

This code goes between the If Target.Value = "Standard price" Then and Range("D9:D10").Locked = True lines. It simply sets the values of D9 and D10 to their standard values whenever "Standard price" is selected.

I hope this helps! Let me know if you have any questions or if there's anything else I can help you with.

 

[SquareCare]

Thank you, GSP75.

This worked for the value check/change and for the font color, but the D9 and D10 cells aren't being set to locked.
Could this have anything to do with the fact I'm using Excel with Swedish as language? This is the code I have, did I miss something?.
I have saved the Excel file as a macro activated worksheet.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$5" Then
If Target.Value = "Standardpriser" Then
If Target.Value = "Standardpriser" Then
Range("D9").Value = 750
Range("D10").Value = 950
End If
Range("D9:D10").Locked = True
Range("D9").Value = 750
Range("D10").Value = 950
Range("D9:D10").Font.ColorIndex = 16 'Gray
ElseIf Target.Value = "Egna priser" Then
Range("D9:D10").Locked = False
Range("D9:D10").Font.ColorIndex = xlAutomatic 'Default black
End If
End If
End Sub

 

[GSP75]

It looks like you have duplicated the same condition `If Target.Value = "If Target.Value = "Standardpriser" Then twice




Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$5" Then
If Target.Value = "Standardpriser" Then
Range("D9").Value = 750
Range("D10").Value = 950
Range("D9:D10").Locked = True
Range("D9:D10").Font.ColorIndex = 16 'Gray
ElseIf Target.Value = "Egna priser" Then
Range("D9:D10").Locked = False
Range("D9:D10").Font.ColorIndex = xlAutomatic 'Default black
End If
End If
End Sub

This code should lock the cells D9:D10 when the value of cell B5 is "Standardpriser", and unlock them and change the font color to default when the value is "Egna priser". If the issue persists, it could be related to the language settings, but it's unlikely.

 

[SquareCare]

Oh, darn.

I used your code above but the problem remains. Do I have to change something in Review -> Protect Sheet?
It says under Format cell -> Protection that inorder for the Locked or Hidden properties to apply I need to protect the sheet.
But when I do that the entire sheet becomes uneditable.

I tried to select allt cells, except B5, and choose Locked in Fotmat cell, but that gave me a Run Time Error 1004 and it says something
about "Legend entries" can't be used with the class Range or something.

 

[SquareCare]

*I selected all cells and UNcecked the Locked box in Format cell in the hopes that the Protect Sheet wouldn't really have any effect except when
the VBA code changed D9:D10 to Locked. But this produces the run time error 1004.

*The 1004 says that the property Locked can't be used for the class Range.

(can't find the edit button for my post/reply...)

 

[GSP75]

2 / 2

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$5" Then If Target.Value = "Standardpriser" Then Range("D9").Value = 750 Range("D10").Value = 950 Range("D9:D10").Locked = True Me.Protect Password:="password" ' Replace "password" with your desired password Range("D9:D10").Interior.ColorIndex = 15 ' Gray ElseIf Target.Value = "Egna priser" Then Me.Unprotect Password:="password" ' Replace "password" with your password Range("D9:D10").Locked = False Range("D9:D10").Interior.ColorIndex = xlNone ' No fill color Me.Protect Password:="password" ' Protect the sheet again End If End If End Sub
This code protects the sheet with a password ("password"), then locks cells D9:D10 and sets their fill color to gray when the value in B5 is "Standardpriser". When the value in B5 is "Egna priser", the sheet is unprotected, cells D9:D10 are unlocked and their fill color is set to none, and the sheet is protected again. Note that you will need to replace "password" with your desired password.

 

[SquareCare]

Hmm, still doesn't work. It now doesn't change it back from protexted to unprotexted and the color doesn't go back to black.

Any chance I can send you the file?

 

[GSP75]

Regarding the issue with the locked property not changing, it's possible that the worksheet is still protected. You could try unprotecting the sheet before trying to modify the locked property, and then re-protect it afterwards. For example:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$5" Then
If Target.Value = "Standardpriser" Then
Range("D9").Value = 750
Range("D10").Value = 950
Range("D9:D10").Locked = True
ActiveSheet.Unprotect ' unprotect the sheet
Range("D9:D10").Locked = True
ActiveSheet.Protect ' re-protect the sheet
Range("D9:D10").Font.ColorIndex = 16 'Gray
ElseIf Target.Value = "Egna priser" Then
ActiveSheet.Unprotect ' unprotect the sheet
Range("D9:D10").Locked = False
ActiveSheet.Protect ' re-protect the sheet
Range("D9:D10").Font.ColorIndex = xlAutomatic 'Default black
End If
End If
End Sub


Range("D9:D10").Font.ColorIndex = xlAutomatic 'Default black
Range("D9:D10").Locked = False
Range("D9:D10").Font.ColorIndex = 1 'black

 

[SquareCare]

I really appriciate all your help and patience.

I've made a workaround since I can't get it to work as intended with VBA. I used your code for the value and color changes.
Then I used custom data validation instead for D9 and D10 to check the value in B5 to prevent editing if they selected Standardpriser.

I'll tinker around a little with your last code block to see if I can get that to work in a seperate file.

Again, many many thanks!
/Nick