PDA

View Full Version : Unlock Blank Cells in Column G



adamsm
07-09-2011, 10:12 AM
Hi anyone,

How could I make the following code to unlock blank cells in column G when a user types a value in cell J10 of the (protected) Active sheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Rng
Dim MyCell
Set Rng = Range("G18:G53")
For Each MyCell In Rng
If MyCell.Value = "" Then

Else: ActiveSheet.Unprotect
MyCell.Locked = True
MyCell.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlNoRestrictions
End If
Next
End Sub


Any help on this would be kindly appreciated.

Thanks in advance.

Zack Barresse
07-09-2011, 10:49 AM
Hi there,

Not sure I quite follow 100%, but perhaps...

Option Explicit

Private Const TRIGGERRANGE As String = "J10"
Private Const MYPASSWORD As String = "type your password here"

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
If Target.Address(0, 0) <> TRIGGERRANGE Then Exit Sub
Me.Unprotect MYPASSWORD
For Each c In Me.Range("G18:G53")
If Len(c.Value) = 0 Then
c.Locked = False
Else
c.Locked = True
c.FormulaHidden = False
End If
Next
Me.Protect MYPASSWORD
End Sub

HTH

adamsm
07-09-2011, 11:13 AM
Thanks for the help. The code unlocks all the rows of column G from row G18:G53.

There are rows which don't contain data in any column within the range. How could I add a line to the code so that it unlocks the cells of column G if the data from Column C to I contains data.

How could I add a line to the code so that the code only unlocks the rows of column G if the adjacent columns contain data.

Zack Barresse
07-09-2011, 12:01 PM
Do all of the columns in that row need to contain data? Or just any cell in that row?

adamsm
07-09-2011, 12:38 PM
Columns C,H,F,I does contain data except G.