Re-enter space in a cell if user accidently deletes it

[James]

I have a column than I input names in. Each cell of that column already has
a space entered in it, so that autocomplete will function. However, if a
user deletes an entry in the column, he deletes the space also. I tried the
following sub in the worksheet change event to solve this problem:

If Not Intersect(Target, wshDatabase.Range("Names")) Is Nothing Then If
Target.Value = "" Then Target.Value = " "

It works fine unless the user selects more than one cell when he deletes
data, in which case I get a "type mismatch" error. How do I correct my sub
so it works with a multi cell selection?

 

[mp]

I have a column than I input names in. Each cell of that column already has
a space entered in it, so that autocomplete will function. However, if a
user deletes an entry in the column, he deletes the space also. I tried the
following sub in the worksheet change event to solve this problem:

If Not Intersect(Target, wshDatabase.Range("Names")) Is Nothing Then If
Target.Value = "" Then Target.Value = " "

It works fine unless the user selects more than one cell when he deletes
data, in which case I get a "type mismatch" error. How do I correct my sub
so it works with a multi cell selection?

is Target a range?
then wouldn't it just be
For Each c in TargetRange If Len( c.Value)=0 Then
c.Value = " "
End If
Next c

 

[Don Guillett Excel MVP]

I have a column than I input names in. Each cell of that column already has
a space entered in it, so that autocomplete will function. However, if a
user deletes an entry in the column, he deletes the space also. I tried the
following sub in the worksheet change event to solve this problem:

If Not Intersect(Target, wshDatabase.Range("Names")) Is Nothing Then If
Target.Value = "" Then Target.Value = " "

It works fine unless the user selects more than one cell when he deletes
data, in which case I get a "type mismatch" error. How do I correct my sub
so it works with a multi cell selection?

First line of event
if target.count>1 then exit sub