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 a formula is entered If .Column <> 9 Then Exit Sub 'Column 9 = I (won't run if not this column) If IsEmpty(Target) Then Exit Sub 'If the range gets cleared End With ' Assign the target cell to a module wide object variable Set rTargetCell = Target 'If a number is entered, run the macro Excuses If IsNumeric(Target) Then Excuses End If 'Explicitly clear memory Set rTargetCell = Nothing End Sub Private Sub Excuses() Application.EnableEvents = False 'Check what was entered into the target cell Select Case rTargetCell.Value 'If a 1 is entered, put "Reason 1" in the cell 'You can change the text between the quotations Case Is = 1 rTargetCell.Value = "Reason 1" 'Same as above for the number 2 Case Is = 2 rTargetCell.Value = "Reason 2" 'enter as many cases as required 'If a number not listed above is entered default to this message. Case Else rTargetCell.Value = "Please call us for further explanation" End Select Application.EnableEvents = True End Sub

How to use:

  1. Open the Visual Basic Editor by going to tools-Macro's-Visual Basic Editor or use Alt-F11
  2. In the project explorer, click on the sheet you wish to use the code on.
  3. In the code panel for the sheet paste the code from above.
  4. Close the Visual Basic Editor by clicking the X in the upper right corner or go to File-Close
 

Test the code:

  1. 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.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express