Originally Posted by
rcbricker
not sure i understand what you did
hi Dream,
yeah I want to enter a 1 in column I and have it change to a given text.
I amended the code to act only on Column I now.
In the Sheet1 code please find:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.HasFormula Or IsEmpty(Target) Then Exit Sub
If Target.Column <> 9 Then Exit Sub
If IsNumeric(Target) Then
Excuses
End If
End Sub
The Worksheet_Change event will check when the worksheet changes.
The first If checks for empty or formula contents in the Target cell; it exits if either is the case. The second If checks to see that the target is in column 9 (I); if not, then it exits. The third if checks to see that the target has a numeric value, if so, it calls the Excuses macro in the ModExcuses. The code for this is below:
Option Explicit
Sub Excuses()
Application.EnableEvents = False
Select Case ActiveCell.Offset(-1, 0).Value
Case Is = 1
ActiveCell.Offset(-1, 0).Value = "Reason 1"
Case Is = 2
ActiveCell.Offset(-1, 0).Value = "Reason 2"
'enter as many cases as required
Case Else
ActiveCell.Offset(-1, 0).Value = _
"Please call us for further explanation"
End Select
Application.EnableEvents = True
End Sub
The Select Case structure tells Excel to place the "Reason 1" text in the cell above the active cell if a 1 is entered. The change event fires once enter or tab is hit; I assumed enter was being used and that the cursor would be below the data entry cell; if tab is being used, switch the Offset(-1,0) to Offset(0,-1) to move back a column. "Reason 2" is entered for 2, etc.
As many cases as there are explanations can be entered using this syntax. Simply replace the text Reason 1 with whatever the appropriate explanation should be.
HTH
Reply With Quote
