Hello again all. Hope you are safe and healthy.
I have the following code on multiple sheets in a workbook. I cannot use XL2BB because of security reasons. so I will have to post it the hard way.
This line of code is giving me a headache.
If the active row column 17 cell is blank, nothing should happen. If the active row column 17 cell has a value, the module Copyemail should run. BUT, it should only run for the active row you are working on and only if the column 17 cell has an entry.
What I am finding is there are times when the code will at random, go BACK to find the last row that has a column 17 entry, and run the module. This happens even if the column 17 cell in the active row is blank. It also happens on random computers. This should not be happening.
I know this is long winded but I am trying to be thorough.
Thank you in advance,
Jim
I have the following code on multiple sheets in a workbook. I cannot use XL2BB because of security reasons. so I will have to post it the hard way.
VBA Code:
Private Sub CommandButton1_Click()
UpdateDataFromMasterFile
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, c As Range
Set r = Union(Range("J6:J5000"), Range("G6:G5000"))
Set r = Intersect(Target, r)
If Not r Is Nothing Then
Application.EnableEvents = False
For Each c In r
Select Case True
Case 10 = c.Column 'J
If c.Value = "" Then
Cells(c.Row, "L").Value = ""
Cells(c.Row, "L").Locked = True
Else
Cells(c.Row, "L").Locked = False
End If
Case 7 = c.Column 'G
If c.Value = "Not Listed" Then
Cells(c.Row, "H").Locked = False
Else
Cells(c.Row, "H").Locked = True
Cells(c.Row, "H").Value = ""
End If
Case Else
End Select
Next c
End If
If Target.Cells.Count > 3 Then Exit Sub
If Not Intersect(Target, Range("C6:C5000")) Is Nothing Then
With Target(1, 3)
.Value = Date
.EntireColumn.AutoFit
End With
End If
Dim p As Range, z As Range
Set p = Range("M6:M5000")
Set p = Intersect(Target, p)
If Not p Is Nothing Then
Application.EnableEvents = False
For Each z In p
Select Case True
Case 13 = z.Column 'M
If z.Value <> "" Then
Check = MsgBox("Are your entries correct?" & vbCrLf & "After entering yes, These values CANNOT be changed.", vbYesNo + vbQuestion, "Cell Lock Notification")
If Check = vbYes Then
Target.Rows.EntireRow.Locked = True
Cells(z.Row + 1, "B").Locked = False
Cells(z.Row + 1, "C").Locked = False
Cells(z.Row + 1, "D").Locked = False
Cells(z.Row + 1, "F").Locked = False
Cells(z.Row + 1, "G").Locked = False
Cells(z.Row + 1, "I").Locked = False
Cells(z.Row + 1, "J").Locked = False
Cells(z.Row + 1, "K").Locked = False
Cells(z.Row + 1, "M").Locked = False
If Cells(Application.ActiveCell.Row, 17).Value <> "" Then Copyemail 'Q
If Cells(Application.ActiveCell.Row, 18).Value <> "" Then ThisWorkbook.Save 'R
With Me
.Parent.Activate
.Activate
.Range("B" & Rows.Count).End(xlUp).Offset(1).Activate
End With
Else
Cells(z.Row, "M").Value = ""
End If
End If
Case Else
End Select
Next z
End If
Application.EnableEvents = True
End Sub
This line of code is giving me a headache.
VBA Code:
If Cells(Application.ActiveCell.Row, 17).Value <> "" Then Copyemail 'Q
What I am finding is there are times when the code will at random, go BACK to find the last row that has a column 17 entry, and run the module. This happens even if the column 17 cell in the active row is blank. It also happens on random computers. This should not be happening.
I know this is long winded but I am trying to be thorough.
Thank you in advance,
Jim