How do I prevent text entering?

  • Thread starter Thread starter Robin Chapple
  • Start date Start date
R

Robin Chapple

I have a spreadsheet where a default in a field is changed by a double
click. A second double click will return the field to the default.

I only need the field to show the 'default' or the 'alternate' entry.
No other entry is needed.

How do I prevent an operator from entering text?

Thanks,

Robin Chapple
 
One way is to use Data Validation
If I knew more of what you are needing I could help a little more
 
Maybe you could use a worksheet event:

Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)

Dim DefaultVal As Variant

DefaultVal = "hi there!"

With Target
If .Cells.Count > 1 Then Exit Sub
If Intersect(.Cells, Me.Range("a1")) Is Nothing Then Exit Sub

Cancel = True 'stop from editing the cell

On Error GoTo ErrHandler:

Application.EnableEvents = False
If .Value = DefaultVal Then
'change it to what you want
.Value = "Good Bye"
Else
.Value = DefaultVal
End If
End With

ErrHandler:
Application.EnableEvents = True

End Sub

Change A1 to the cell's address that you need.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Chip Pearson has some instructions on events:
http://www.cpearson.com/excel/Events.aspx

David McRitchie has some notes, too:
http://www.mvps.org/dmcritchie/excel/event.htm

And the macro will not work if the user disables macros or disables events.
 
Back
Top