Validating cell entry is not working - Excel VBA 2

[alwayslrN]

I am using Excel 2002 and have a field that upon the workbook opening populates cell F7 with the following:

wsMainMenu.Range("F7").Value = CStr(Format(Now(), "'mmyyyy"))

The user of the workbook can override this value, but I want to verify that the format is the same as above, a string and as mmyyyy

I tried playing around with

Worksheet_SelectionChange(ByVal Target As Range)

however, though the value in the field is in the correct format and is a string the message I created in case the value is not correct keeps poping up. I have no idea what I am doing wrong OR perhaps I do not understand how this event works OR both.

 

[Fr33dan]

I'd be happy to help but it'd be easier if we could see the code that is actually giving the the message

 

[VRoscioli]

Posting your validation code may help discern what the problem is.

-V

 

[SkipVought]

Why are you using SelectionChange?

You only want to know then the user CHANGES the VALUE in the cell, correct?

Use the Worksheet_Change event and show us your validation code.

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[alwayslrN]

I want to ensure if the user changes the default value that they change it to the correct format and data type. In the example above when the workbook opens it defaults to 072007.

If the user changes the value, I want to be sure they change to 062007 and not 62007 or something else.

Here is one of things I tried

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Not Intersect(Target, Range("F7")) Is Nothing Then
        If IsNumeric(wsMainMenu.Range("F7").Value) or Len((wsMainMenu.Range("F7").Value)) <> 6 Then
            MsgBox "The format must be mmyyyy, please change"
        End If
    End If

End Sub

 

[PHV]

You meant this ?
If [!]Not[/!] IsNumeric(wsMainMenu.Range("F7").Value) Or Len((wsMainMenu.Range("F7").Value)) <> 6 Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[alwayslrN]

Thanks PHV that almost did it. The problem now is the message does not happen upon changing the value to an errant value. It only happens if I leave the cell and then select the cell.

Thanks as always for your help.

 

[PHV]

Use the Change event instead of the SelectionChange.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Did you read my earlier post???

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[alwayslrN]

Sorry skip - I read it, but somehow missed that last line. Thanks