force user to edit a cell

[ls62]

Hi

I'm validating cells on worksheet change and if the value is invalid I'm trying to force the user into edit mode in that cell. I tried using the sendkeys {f2} at the end of the procedure, but this doesn't seem to be working consistently.

I want to force the user to edit the cell until they have a valid entry. Can someone help or suggest how to do this?

Here's my procedure:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim EAddr As String, EMsg As String

'
' Test for valid email address
'
If Target.Column = 6 Then
    EAddr = Cells(Target.Row, Target.Column).Value
    If ValidateEmail(EAddr, EMsg) = False Then
        MsgBox (EMsg)
        Cells(Target.Row, Target.Column).Select
        SendKeys "{F2}", False
        
    End If
End If
End Sub

Thanks

LEE

 

[ck1999]

Why not us an inputbox

If Target.Column = 6 Then
    EAddr = Cells(Target.Row, Target.Column).Value
    If ValidateEmail(EAddr, EMsg) = False Then
        do while validateemail(EAddr, EMsg) = false
            ans = inputbox (EMsg)
        loop 
        Cells(Target.Row, Target.Column).value = ans
        
    End If
End If

ck1999

 

[Bong]

Would the "validation" tool on the spreadsheet, itself, work for you (it doesn't force anything but it can be very insistent)?

_________________
Bob Rashkin

 

[ls62]

ck1999

I could do that but I didn't want to pop up a window for them to edit or re-enter in. I was thinking it would be better just to send them back into excel in edit mode of that cell. There they would either correct it or blank it out if they didn't have a real address.

 

[ls62]

bong

I'm validating the email address, I don't think the validation tool will work this, will it?

thanks

 

[combo]

It is rather brute, but you can set the ScrollArea to the non valid range, and reset it when entry data is ok.

combo

 

[ls62]

There must be some way to put he user into edit mode in a cell, right?

Setting the scroll area won't put them into edit mode, it will just not allow them out of the range, am I correct?

Thanks

 

[combo]

There is no customised cursor position inside the cell, it is possible to terminate edition with ESC key, I would rather consider alternative way. Isn't the ScrollArea (together with a kind of message to the user) a method to force him to enter proper data? If you like to stay with edit mode, use either userform or add a textbox to the worksheet:

Private Sub TextBox1_LostFocus()
If Me.TextBox1.Text <> "x" Then Me.TextBox1.Activate
End Sub

combo

 

[ls62]

combo

I'm no expert at this, so how would I set the scroll area and then unset it once they have an acceptable value in the cell. Setting the scroll area wouldn't put them into edit mode, right... they'd still have to press F2 or click the cell to get into edit it.

Thanks

 

[SkipVought]

Re: Data > Validation...

"I'm validating the email address, I don't think the validation tool will work this, will it?"

Your ValidateEmail(EAddr, EMsg) function (I don't know what EMsg has to do with validating an eMail address) can be used in the CUSTOM validation. It will only allow an entry, if the return value is TRUE.

CAVEAT: ValidateEmail must be in a MODULE.


Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[ls62]

Skip

EMsg is a error message returned back from the module. Tells the user what is wrong with the email address. Yes, validatemail is in a module.

I didn't realize you could do custom validation there. I'll give it a try and see how it work.

Thanks

 

[SkipVought]

Normally, you'd return a value to the Function like...

Function ValidateEmail(EAddr) as Long
  On Error resume Next
' you logic goes here
'.......
' and finally when all is done...
' when there is no error ValidateEmail returns a 0

   ValidateEmail = Err.Number

End Function

So when you use the function...
[tt]
=ValidateEmail(A1)=0
[/tt]
is TRUE when the email reference is good and FALSE when it is not.



Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[ls62]

Maybe I'm missing something. When I go to data --> validation and select custom and try to add the validemail function in the formula box it tells me that the range is invalid.

I setup another version, simpler function called validemail(Email) to return a true/false and I get this error.

When I put in something like '=validemail(f7)' in the formula box I get an error. Am I doing something wrong?

Thanks

 

[SkipVought]

[tt]
=validemail(f7)
[/tt]
This Data > Validation is in F7 AND F7 contains an eMail address?

Please post your validemail function code.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[ls62]

Skip,

Yes, I was putting the data>validation into the F7 and F7 cell would have the email address in it.

Basically the entire column 'F' needs the validation and each cell in that column would be blank or contain a email address that needs to be valid when entered.

Here's the code

Function ValidEmail(Email As String) As Boolean
' Returns true if given a valid email address or Null
    'setup
    Dim TempCheck As Boolean
    Dim TempCount, TempLoop As Integer
    Dim TestData As Variant
    TempCheck = True
    TempCount = 0
    TempLoop = 0
    If Not IsNull(Email) Then 'don't bother checking if email is empty
        'check of invalid characters
        For TempLoop = 1 To Len(Email)
            Select Case Asc(Mid(Email, TempLoop, 1))
                Case 0 To 45
                    TempCheck = False
                Case 47
                    TempCheck = False
                Case 58 To 63
                    TempCheck = False
                Case 91 To 94
                    TempCheck = False
                Case 96
                    TempCheck = False
                Case Is > 122
                Case Else
            End Select
        Next TempLoop
    
        'check for 1 "@" character only
        TempCount = 0
        TempLoop = 0
        Do
            TempLoop = InStr(TempLoop + 1, Email, "@")
            If TempLoop > 0 Then
              TempCount = TempCount + 1
            End If
        Loop Until TempLoop = 0
        If TempCount > 1 Or TempCount < 1 Then TempCheck = False
    End If
    'return result
    ValidEmail = TempCheck
End Function

 

[combo]

In reply to ScrollArea usage (validation function for illustration only):

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 6 Then
    If ValidateEmail(Target.Value) Then
        Me.ScrollArea = ""
        SetComment False, Target
    Else
        Me.ScrollArea = Target.Address
        SetComment True, Target
    End If
End If
End Sub

Private Sub SetComment(bSet As Boolean, rCell As Range)
With rCell
    If bSet Then
        If .Comment Is Nothing Then .AddComment
        .Comment.Visible = True
        .Comment.Text Text:="E-mail addres edition" & Chr(10) & "please write correct address"
    Else
        If Not .Comment Is Nothing Then .ClearComments
    End If
End With
End Sub

Private Function ValidateEmail(EAddr) As Boolean
Randomize Timer
If Rnd > 0.5 Then
    ValidateEmail = False
Else
    ValidateEmail = True
End If
End Function

combo

 

[SkipVought]

Here's some code in the SHEET OBJECT...

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, [EmailRange]) Is Nothing Then
        If Not ValidEmail(Target.Value) Then
            Target.ClearContents
            Target.Select
        End If
    End If
End Sub

where the data > validation area is NAMED EmailRange

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[SkipVought]

...oh yes, I was not able to use the UDF in the Data > Validation.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[ls62]

Skip / Combo

Thanks for the suggestions and help. I will try these and see how they work. Although I think your solutions have it so that the incorrect cell will remain selected I have yet to figure out how to 'force' that cell into edit mode.

Any idea on this?