PDA

View Full Version : Solved: Msgbox for if beginning letter of word a "M"



wilg
06-01-2011, 07:33 PM
Hi there. Its there some way I can prompt a msgbox for when someone keyes in a word or letters in a cell that if the beginning letter is a "M" or "m" then a msgbox to appear to let them know to key in full 6 digits of the word?

Or if beginning letter is a "M" or "m" and word is less tha 6 digits to prompt a msgbox?

Bob Phillips
06-01-2011, 09:24 PM
You could use data validation with a custom formula of

=OR(AND(LEFT(I2,1)="M",LEN(I2)=6),LEFT(I2,1)<>"M")

(assuming the DV is in cell I2)

GTO
06-02-2011, 01:44 AM
Only if you didn't know where they might be entering such strings, I had come up with this.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewVal As String
Dim bolCorrected As Boolean
Dim bolEventsEnabled As Boolean

If Target.Count >= 1 Then
If Len(Target.Value) Then
If (Asc(Target.Value) = 77 Or Asc(Target.Value) = 109) _
And Not Len(Target.Value) = 6 Then
bolEventsEnabled = Application.EnableEvents
Application.EnableEvents = False
Do
NewVal = InputBox( _
Prompt:="You entered """ & _
IIf(Not NewVal = vbNullString, NewVal, Target.Value) & """." & vbCrLf & _
"You MUST enter the full six-digit code." & String(2, vbCrLf) & _
"Re-enter or click the <Cancel> button to empty the cell", _
Title:="Input Error...", _
Default:=IIf(Not NewVal = vbNullString, NewVal, Target.Value))

If NewVal = vbNullString Then
Target.ClearContents
Exit Do
ElseIf (Asc(NewVal) = 77 Or Asc(NewVal) = 109) _
And Len(NewVal) = 6 Then
bolCorrected = True
Target.Value = NewVal
End If
Loop While Not bolCorrected
Application.EnableEvents = bolEventsEnabled
End If
End If
End If
End Sub

Of course you probably do know where they'd be entering the date, in which case, of course the DV is just wayyyy brighter. :omg2:I have to go see where I left my brain now...

Chabu
06-02-2011, 02:15 AM
Don't you remember? You donated it to science years ago!:wot

wilg
06-02-2011, 05:13 AM
DV worked like a charm. Good job. Thanks very much guys.