How to set a cell's default value as constant in excel

Question

Second post and question. Still quite the newbie.

How do I set the default value of certain cells, to 9999 for example, so that even if someone enters a value in that cell, then delete's their entry it refers back to that default value?

Does that make sense?

Step by step..

1)Cells C9:C21 have nothing entered so they all say "9999"

2)User enters "45" into C12

3)User realizes mistake and deletes entry from C12 and enters it into C15

4)Cell C12 should revert back to default number/value

Is this doable?

(Am open to all solutions)

Thanks.

Answer 1

Place the following Event Macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim C As Range, inter As Range, r As Range
   Set C = Range("C9:C21")
   Set inter = Intersect(C, Target)
   If inter Is Nothing Then Exit Sub

   Application.EnableEvents = False
      For Each r In inter
         If r.Value = "" Then r.Value = 9999
      Next r
   Application.EnableEvents = True
End Sub

Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

Macros must be enabled for this to work!