Hello All. I did not see an answer to my question already posted. If it has been posted Let me know . I have A workbook with multiple sheets. Except sheet 1, all are nearly carbon copies of each other. I already have one set of "Private Sub Worksheet_Change(ByVal Target As Range)" code on each sheet. It locks cells in column H until the values in G are entered. It will delete and re-lock H if the value in G is removed. It is as follows:
I then tried to add the following code to automatically add the date in another column.
Until now, I did not know I could not have two "Private Sub Worksheet_Change codes in the same sheet. How do I get around this? There is also other code on the worksheet. Some for an active x button, some for opening and closing a master file. I did not add all the code in this initial post. If I need to let me know.
Thanks in advance,
Jim
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, c As Range
Set r = Range("G6:G5000")
Set r = Intersect(Target, r)
If r Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each c In r
Select Case True
Case 7 = c.Column 'G
If c.Value = "" Then
Cells(c.Row, "H").Value = ""
Cells(c.Row, "H").Locked = True
Else
Cells(c.Row, "H").Locked = False
End If
Case Else
End Select
Next c
Application.EnableEvents = True
End Sub
I then tried to add the following code to automatically add the date in another column.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 4 Then Exit Sub
If Not Intersect(Target, Range("B6:B5000")) Is Nothing Then
With Target(1, 4)
.Value = Date
.EntireColumn.AutoFit
End With
End If
End Sub
Thanks in advance,
Jim