View Full Version : Worksheet change - single click>value entered

10-15-2009, 08:59 AM

Is it possible to set up a worksheet change function so that if a user single clicks a cell a value is entered automatically into that cell? If so, can the function allow for the use of the delete button?

I guess a single click toggle between no value & the specified value?

All I've been able to come up with is below, but I'm still quite new to worksheet changes, etc. It requires a double click, and delete won't work (because it entails more clicks!).


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
Target.Value = "Nil"
End Sub

10-15-2009, 09:52 AM
I don't think this is everything you want, but see if it helps:Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Cells(1, 1)) Is Nothing Then Exit Sub
Application.EnableEvents = False
Application.EnableEvents = True
If Target = vbNullString Then Target = 1: Exit Sub
If Target = 1 Then Target = vbNullString
End Sub