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]

Without seeing your sheet, I'm only guessing at columns...but give this a try:

Private Sub worksheet_change(ByVal target As Range)
If Not Intersect(target, Range("A1:A10")) Is Nothing Then
    If target.Value = "" Then
        target.Value = target.Row
    End If
End If
End Sub

 

[Pinaceous]

Without seeing your sheet, I'm only guessing at columns...but give this a try:

Private Sub worksheet_change(ByVal target As Range)
If Not Intersect(target, Range("A1:A10")) Is Nothing Then
    If target.Value = "" Then
        target.Value = target.Row
    End If
End If
End Sub

Hey Candyman8019!

Your code really works well.

You have guessed very well.

I just have something to ask you to add.

Referencing my picture below:

"My line 11 corresponds to 1.
My line 12 corresponds to 2.
...
My line 20 corresponds to 10."

Do you know how to adjust your code to compensate for this?

Other than that it works very well!

Thank you!
pinaceous

 

[Candyman8019]

You can simply subtract 10 from the row number in that case...

Private Sub worksheet_change(ByVal target As Range)
If Not Intersect(target, Range("A1:A10")) Is Nothing Then
    If target.Value = "" Then
        target.Value = target.Row -10
    End If
End If
End Sub

 

[Pinaceous]

Candyman8019!

Wow!! That is so cool!

One last thing, if I may.

I tried to mess around with the Format Cell sheet feature, but now how to I return each name with a "#" in front of it without typing one?

For example, if I type in the cell A14 "David" it will return "#David" and also for example if I type in cell A15 "Sam" it will return "#Sam".

Please let me know.

Thank you!
pinaceous

 

[Candyman8019]

Try this

Private Sub worksheet_change(ByVal target As Range)
If Not Intersect(target, Range("A1:A10")) Is Nothing Then
    If target.Value = "" Then
        target.Value = target.Row -10
    Else
         Target.value = “#” & target.value
    End If
End If
End Sub

 

[Pinaceous]

Hi Candyman8019,

The code works really well! Thank you so much for working on this!

However, I just would like to know, if you can add to the code that IF the cell gets deleted and nothing is entered into it IF it can revert back to the original number without the "#"?

Also, can you provide that only one "#" is placed per entry of the cell?

For example, if I delete A12 entry of (2) it changes to (#2) ...

Can you provide that if I delete A12 its entry of (2) will revert back to (2) and not (#2) ... ?

Also, can you provide that if an entry is made that only one (#) is provided?

For example, If I provide (David) for A16 it will provide (#David) and not (##David) ...

Thank you!
pinaceous

 

[Candyman8019]

I’m not at a computer to test it out but, give this a try.

Private Sub worksheet_change(ByVal target As Range)
If Not Intersect(target, Range("A1:A10")) Is Nothing Then
Application.enableevents = false
    If target.Value = "" Then
        target.Value = target.Row -10
    Else
         Target.value = “#” & target.value
         Target.value = Replace(target.value, “##”,”#”)
    End If
Application.enableevents = true
End If
End Sub

 

[Peter_SSs]

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

 

[Pinaceous]

I’m not at a computer to test it out but, give this a try.

Private Sub worksheet_change(ByVal target As Range)
If Not Intersect(target, Range("A1:A10")) Is Nothing Then
Application.enableevents = false
    If target.Value = "" Then
        target.Value = target.Row -10
    Else
         Target.value = “#” & target.value
         Target.value = Replace(target.value, “##”,”#”)
    End If
Application.enableevents = true
End If
End Sub

Thank you Candyman8019!