|
|
|
|
|
|
Excel
|
Pre-determined text entered in cell when number is entered.
|
|
Ease of Use
|
Easy
|
Version tested with
|
2000
|
Submitted by:
|
lucas
|
Description:
|
You type a number in a cell and instead of the number, a line of text is entered.
|
Discussion:
|
You enter repetitive data in a column but not consecutively so that you can drag it down. This script will allow you to enter a number and text associated with that number will be entered in the cell. You can have as many numbers and associated texts as you desire using "case is". Typing in a number not listed will result in a default message. Column choice is configurable also.
|
Code:
|
instructions for use
|
Place this code In the sheet code module you wish To use it on:
Option Explicit
Dim rTargetCell As Range
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .HasFormula Then Exit Sub
If .Column <> 9 Then Exit Sub
If IsEmpty(Target) Then Exit Sub
End With
Set rTargetCell = Target
If IsNumeric(Target) Then
Excuses
End If
Set rTargetCell = Nothing
End Sub
Private Sub Excuses()
Application.EnableEvents = False
Select Case rTargetCell.Value
Case Is = 1
rTargetCell.Value = "Reason 1"
Case Is = 2
rTargetCell.Value = "Reason 2"
Case Else
rTargetCell.Value = "Please call us for further explanation"
End Select
Application.EnableEvents = True
End Sub
|
How to use:
|
- Open the Visual Basic Editor by going to tools-Macro's-Visual Basic Editor or use Alt-F11
- In the project explorer, click on the sheet you wish to use the code on.
- In the code panel for the sheet paste the code from above.
- Close the Visual Basic Editor by clicking the X in the upper right corner or go to File-Close
|
Test the code:
|
- Type a number in Column I. If you type a 1 you will get "Reason 1" If you type a 2 you will get "Reason 2" If you type any other number you will get a default statement. If you type text in the cells, it will stay as you enter it.
|
Sample File:
|
predefined_txt_when_number_entered.zip 8.43KB
|
Approved by mdmackillop
|
This entry has been viewed 149 times.
|
|