tiredofit
Well-known Member
- Joined
- Apr 11, 2013
- Messages
- 1,834
- Office Version
- 365
- 2019
- Platform
- Windows
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:
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:
but can that be set using code?
Thanks
Here is the code:
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:
Code:
[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