How to run code when cell value changes from empty to entered value
Category: [Excel] Demo Available
How to run code when cell value changes from empty to entered value
Requirement:
The user has a cell range B3:B327 that is initially entered by user & several other cells are activated by the entry. The user is trying to run a code when cell values change from empty to entered values.
Solution:
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
Obtained from the OzGrid Help Forum.
Solution provided by FrankM.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to copy the entire sheet and paste as values - running on multiple tabs |
| How to use a macro to run through sheet in excel and put double quotes around values |
| How to create a button to run formula on every sheet in workbook |
| How to create and auto run macro if value on cell A1 is less than value on B2 |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.