Validating data

[tiredofit]

I am trying to validate some data on my worksheet. If a user enters a value that contains a space, I want that action reversed.

Here is the code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    If InStr(1, Target.Value, " ", 0) <> 0 Then
          
        With Application
       
            .EnableEvents = False
           
            .Undo
           
            .EnableEvents = True
       
        End With
       
    End If
                   
End Sub

It works well if you typed something like "some nonsense" or even "some " (obviously without the "").

However, if you typed "some@ nonsense", it first converts it to a hyperlink with it underlined, ie "some@ nonsense", then the undo action kicks in so reverses the string to "some@ nonsense"

What I want is the cell to be blank.

How can I amend my code to do that?


EDIT: Seems I need to set this option:

[LIST]
[*]File-> Options-> Proofing-> AutoCorrect Options…-> AutoFormat As You Type-> uncheck: Internet and network paths with hyperlinks
[/LIST]

but can that be set using code?



Thanks

 

[tiredofit]

As your code works quite happily for me, I don't know what to suggest.

No probs, it's a minor thing.

However, would welcome your thoughts on this thread:

Allow users to intercept when code is running

One way to to allow users to intercept code when it's running is to use DoEvents. The problem I have is despite having added DoEvents, my program crashes. I do have a working version and will explain later. Firstly in wksData, I have 5 columns of data. Column A contains date, columns B through...

www.mrexcel.com