Change Cell Default Value

[Pinaceous]

Good Day,

How do I change the Cell Default Value from zero (0) in excel to return a different number or value?


For example, How would I provide a number list (Left Picture) to then have the user input their name in a cell to then remove the placeholder value (Right Picture).

Also, if the user removes their name, it will then return back to the cell's default value.

Please let me know.

Thank you!

Respectfully,
pinaceous

PS. VBA / CF?

 

[Candyman8019]

Please see Peter’s solution…he took it a step farther and is a much better approach.

 

[Pinaceous]

In the event that more than one cell might be altered at once (eg #David & #Sam as in post 5 are selected and deleted together) the code would error and you may end up being left with "Events" disabled. To avoid such an error and ensure that each row gets the correct number entered, I would suggest this modification.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
 
  On Error GoTo PreExit
  Set Changed = Intersect(Target, Range("A11:A20"))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      If Len(c.Value) = 0 Then
        c.Value = c.Row - 10
      ElseIf Left(c.Value, 1) <> "#" Then
        c.Value = "#" & c.Value
      End If
    Next c
  End If
PreExit: Application.EnableEvents = True
End Sub

Thank you very much Peter_SSs!!

That was really great forward intuitive thinking!

But I have a question for you. The code works absolutely fine on a fresh workbook.

But when I place the code in my "working" workbook it provides:

When I delete the number from cell A11 of (1) it returns (#1)...

Again, your code works absolutely fine on a fresh workbook but am wondering if you can help me to figure out why this is happening on my "working" workbook?

Just trying to narrow down some possibilities with your assistance.

Thanks again for your valued help!

Respectfully,
pinaceous

 

[Peter_SSs]

The code works absolutely fine on a fresh workbook.

But when I place the code in my "working" workbook it provides:

You must have some other/more code or custom formatting in that workbook.
If you are not able to identify the problem, please post the entire Worksheet_Change code from that sheet's module.
Also check the ThisWorkbook module for any code, in particular any Workbook_SheetChange code.

 

[Pinaceous]

You must have some other/more code or custom formatting in that workbook.
If you are not able to identify the problem, please post the entire Worksheet_Change code from that sheet's module.
Also check the ThisWorkbook module for any code, in particular any Workbook_SheetChange code.

Thanks Peter_SSs!

That actually did the trick!

Thank you for your help!

Respectfully,
pinaceous

 

[Peter_SSs]

You're welcome. Glad you got it sorted.