How to implement a resettable, over-ridable, default cell value in Excel?

@SiddharthRout: I will still upload a current copy of the file for your perusal. Parts of my question have been answered, but there are still the two items from my 'Wish list' to be done with yet! –

As per my earlier suggestion, the current code that you are using should be written as

Private Sub Worksheet_Change(ByVal Target As Range)    On Error GoTo Whoa    If Not Intersect(Target, Range("J6")) Is Nothing Then        Application.EnableEvents = False        ActiveSheet.Unprotect ("012370asdf")        If Application.Evaluate("=IF(COUNTIF(C3:F315,J6),VLOOKUP(J6,C3:F315,4,FALSE),""~"")") = "~" Then            Range("M6").Value = "~"            Range("M6:M7").Locked = True        Else            Range("M6").Formula = "=IF(COUNTIF(C3:F315,J6),VLOOKUP(J6,C3:F315,4,FALSE),""~"")"            Range("M6:M7").Locked = False        End If        ActiveSheet.Protect ("012370asdf")    End IfLetsContinue:    Application.EnableEvents = True    Exit SubWhoa:    MsgBox Err.Description    Resume LetsContinueEnd Sub

This also negates the use of an extra cell N6.

I am looking at the rest of the stuff now and will update it soon.

UPDATE: Both your requests in the WishList are complete.

Your Worksheet_Change event now becomes this to incorporate Wish List 1 (See Snapshot attached)

Private Sub Worksheet_Change(ByVal Target As Range)    Dim vVal As Variant    On Error GoTo Whoa    vVal = Application.Evaluate("=IF(COUNTIF(C3:F315,J6),VLOOKUP(J6,C3:F315,4,FALSE),""~"")")    If Not Intersect(Target, Range("J6")) Is Nothing Then        Application.EnableEvents = False        ActiveSheet.Unprotect ("012370asdf")        '~~> Check the value of the CheckBox and update cells only if false        '~~> This is valid for "~" as well i.e if the checkbox is Checked then        '~~> even "~" remain unchanged. If you don't want this, then move the         '~~> below condition inside "ELSE" part :)        If OLEObjects("Checkbox1").Object.Value = False Then            If vVal = "~" Then                Range("M6").Value = "~"                Range("M6:M7").Locked = True            Else                Range("M6").Value = vVal                Range("M6:M7").Locked = False            End If        End If        ActiveSheet.Protect ("012370asdf")    End IfLetsContinue:    Application.EnableEvents = True    Exit SubWhoa:    MsgBox Err.Description    Resume LetsContinueEnd Sub

For your 2nd Wish List I had two options. I went ahead with the 2nd option.

1) Use the method as described in www.ozgrid.com

Topic: Auto Complete Typing in an Excel Data Validation List

Link: http://www.ozgrid.com/Excel/autocomplete-validation.htm

And

2) Use a control instead of DV List. For this, I made these changes in the list

• Remove Data validation in Cell J6
• Gave a "Name" to your list X3:X315 from Name Manager. I called it "List"
• Placed a ComboBox on top of Cell J6 and set the .ListFillRange to the above "List" in design mode
• Added the below code to the worksheet code area

CODE

Private Sub ComboBox1_Click()    Range("J6").Value = ComboBox1.ValueEnd SubPrivate Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _ByVal Shift As Integer)    If KeyCode = 13 Then        Range("J6").Value = ComboBox1.Value    End IfEnd Sub`

Now your ComboBox will auto-complete whenever you type anything in the box.

SNAPSHOT

SAMPLE FILE LINK (This link is active for 7 days)

Sample File

HTH

Sid

I'm not at my PC right now, so I can't test this but here is what you need to do:

Number 2 in your "Information found so far" is the right direction to go, even though you said that you didn't want macros.

Move your formula for the Stack cell into another cell that isn't being used. Lock this cell, and set the background and text colors to be the same (so that it is "hidden"). For now, let's say that this is in O6. (Or just have this cell on another sheet that they can not access. I often have a hidden sheet just for these.)

Right-click on the worksheet tab, and select View Code.In the new window, double-click the worksheet name that you want this code to run on.

Private Sub Worksheet_Change(ByVal Target As Range) should be the default function that appears (and it will be empty).

Place the following code into the Worksheet_Change routine:

Private Sub Worksheet_Change(ByVal Target As Range)    Dim rng As Range    Set rng = Intersect(Range("J6"), Target)    'If J6 has not been changed, then exit.  Otherwise continue.    If rng Is Nothing Then        Exit Sub    Else        'Replace password with the password that you use to protect the sheet (two places)        ActiveSheet.Unprotect ("password")        If Range("O6").Value = "~" Then            Range("M6").Value = "~"            Range("M6:M7").Locked = True        Else            Range("M6").Value = Range("O6").Value            'Use M6:M7 here instead of just M6 because cells are merged.            Range("M6:M7").Locked = False        End If         ActiveSheet.Protect ("password")    End IfEnd Sub