Trying to run excel vba code when cell value changes from empty to entered value.


Joined
Dec 13, 2017
Messages
7
Reaction score
1
I am have cell range B3:B327 that is initially entered by user & several other cells are activated by the entry.
I currently have code that runs when there is a change in column M. Here is the code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
'Instructs user what to do once pending is entered in Column M.
If Intersect(Target, Range("M:M")) Is Nothing Then Exit Sub
If Target.Value = "Pending" Then
MsgBox "Enter the name on the Outlook Calendar, for a letter (Date in Red & Cancel 2 weeks later. If No response)." & vbCrLf
Else
End If
End Sub


I have tried modifying the above code, if the value changes in Cell range B3:B327. I get no response using <>. I have found code looking for specific text, but not going from empty. A friend tried to help my & I had the same response, (No response). Here is his code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("B2:B17")) Is Nothing Then Exit Sub
Dim OldVal As Variant, NewVal As Variant
Application.EnableEvents = False
NewVal = Target.Value
Application.Undo
OldVal = Target.Value
' Test Cell's Previous State
If IsEmpty(OldVal) Then
MsgBox " Cell Originally Empty ..."
Else
MsgBox " Cell already Populated ..."
End If
' Accept New Value
Target.Value = NewVal
Application.EnableEvents = True
End Sub


What am I doing wrong?
 
Ad

Advertisements

Joined
Dec 13, 2017
Messages
7
Reaction score
1
I resolved the issue. Here is the finished code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
'Instructs the user to copy the Social Security number after entering the name.
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("c3:c327")) Is Nothing Then Exit Sub
Dim OldVal As Variant, NewVal As Variant
Application.EnableEvents = False
NewVal = Target.Value
Application.Undo
OldVal = Target.Value
' Test Cell's Previous State
If IsEmpty(OldVal) Then
Application.Speech.Speak "Copy the Social Security Number directly from C. P. R. S. The system stips the first five numbers. ", SpeakAsync:=True
MsgBox " Copy the Social Security Number directly from CPRS. The system strips the first five numbers. ", vbInformation, "Vocational Services Database - " & ActiveSheet.Name
Else

Application.Speech.Speak " You just erased the entry that was there. Is that what you wanted to do?", SpeakAsync:=True
MsgBox " You just erased the entry that was there. Is that what you wanted to do?"
End If
' Accept New Value
Target.Value = NewVal
Application.EnableEvents = True
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top