How can I click on a cell and enter a default value?

[pawlingJohn]

I would like to be able to click on a cell and have a value automatically
entered. I would like this to work like a forms control checkbox. Or is my
only answer use the control checkbox?

 

[Don Guillett]

Try using

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$E$2" Then Target = 234
End Sub

 

[pawlingJohn]

Don,

Thanks for the quick reply but i have never worked with VB and am not sure
what to do once I put the code in.

 

[Don Guillett]

Right click the sheet tab>view code>copy/paste this.
Now when you select cell E2 the number 234 will appear in that cell.

 

[pawlingJohn]

Thanks Don. One more question. If I put an "X"instead of a number it
doesn't insert anything.

 

[pawlingJohn]

Opps!!! I have to put the X in quotes.

Thanks a millions Don.

 

[Don Guillett]

Glad you got it figured out.

 

[PawlingJohn]

One last question. How can i do this so that only a click will insert the
desired input. Now whenever i tab past the field the value goes into it.

 

[Gord Dibben]

You could use a double-click event instead.

Private Sub Worksheet_BeforeDoubleClick(ByVal _
Target As Range, Cancel As Boolean)
If Target.Address = "$E$2" Then Target = 234
Cancel = True
End Sub


Gord Dibben MS Excel MVP