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

 

[Fluff]

If you want a blank cell just replace the .Undo with Target.Value=""

 

[tiredofit]

If you want a blank cell just replace the .Undo with Target.Value=""Nice thought, thanks!

Nice thought, thanks!

I always immediately think of .Undo.

With some of my other programs, that doesn't even work because the stack is cleared. For those situations, I make use of the Selection_Change event, recording what the value was before the change, then reverse it.

 

[Fluff]

For what it's worth I don't get the same behaviour you are complaining about, your code clears the cell (if it was blank before)

 

[tiredofit]

For what it's worth I don't get the same behaviour you are complaining about, your code clears the cell (if it was blank before)

Oh no, your code works well for this situation.

I was referring to other situations in other programs I have written, eg 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

 

[Fluff]

I tend to steer clear of using Undo & rely on the users doing things correctly. It's far easier.

 

[tiredofit]

I tend to steer clear of using Undo & rely on the users doing things correctly. It's far easier.

So I take it you don't work in finance!

 

[Fluff]

Not sure that makes any difference, it's a matter of educating users & trusting them. Code should be a tool to help them do their job.

 

[tiredofit]

Not sure that makes any difference, it's a matter of educating users & trusting them. Code should be a tool to help them do their job.

Joking aside, one reason I want to use .undo for this particular problem is if the user types a value over an existing value.

Blanking it would erase the existing record whereas undo reverts it.

 

[Fluff]

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