Unlock Cell from Another cells value

BettaJ

New Member
Joined
Feb 25, 2020
Messages
11
Office Version
  1. 2007
Platform
  1. Windows
I'm appologize if this topic has been covered...I just couldn't find the right one that would work.

I have an excel, where Column D is a value used in a look up for column E,F,G, and R.
So when I put in 12345 it will automatically fill in the lookup values and fill in the E,F,G, and R. (duh, I know)
One value for D is "MISC".
When I put this one in, the values for E,F,G, and R are blank as I have to put those in manually.

So in order to keep my excel from being edited or [formulas] seen, I protected the sheet.
And for this, I wanted a VBA that would allow me to UNLOCK cells E,F,G, and R when the value of D is "MISC".
*Keep in mind cells E,F,G, and R all have a lookup formula in them as they connect with D's value.

For testing purposes, I just tried only column F and R as it seems the VBA function of RANGE only allows Cell1 and Cell2 so my fomula is my Sheet Code is as follows:
Private Sub Worksheet_Change(ByVal Target As Range)
Me.Unprotect ""

If Not Intersect(Target, Range("D3:D100")) = "MISC" Then
Range(Cells(Target.Row, "F"), Cells(Target.Row, "R")).Locked = False

End If
Me.Protect ""
End Sub


I have no clue how to do this as I believe it unlocks that whole row...even the G column that wasn't in the VBA.
Moreover, I get some error code when I fill in values for random cells [on that line] stating some ERROR CODE 91.

I would appreciate any help...
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,287
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Me.Unprotect ""

If Not Intersect(Target, Range("D3:D200")) Is Nothing Then
   Intersect(Target.EntireRow, Range("F:G,R:R")).Locked = Not Target.Value = "MISC"
End If
Me.Protect ""
End Sub
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

BettaJ

New Member
Joined
Feb 25, 2020
Messages
11
Office Version
  1. 2007
Platform
  1. Windows
Another issue arises... (sorry)

So when I type in MISC my E will be a look up, (so will be my F,G,& R)
The reason why I unlocked those FGR is b/c I wanted to manually type in.

BUT If i initially put in MISC for D and filled in the FFR manually...but decided to not put this in the MISC.
I delete MISC from D but whenever I put some value in D that is defined with a lookup for FGR, those fomulas disappear..
Anyway ONLY KEEP MISC to unlock and if it's NOT MISC it'll ALWAYS keep FGR with the cell's lookup formula still in tact??

does that make sense?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,287
Office Version
  1. 365
Platform
  1. Windows
That code does not change what's in the cells. If you have already overwritten the formulae, then you will need to put it back in.
 

BettaJ

New Member
Joined
Feb 25, 2020
Messages
11
Office Version
  1. 2007
Platform
  1. Windows
Got it! Thank for your help Fluff.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,287
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
If you need help putting the formulae back in just shout
 

Watch MrExcel Video

Forum statistics

Threads
1,114,314
Messages
5,547,167
Members
410,775
Latest member
alal1030
Top