View Full Version : Solved: Msgbox for if beginning letter of word a "M"
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)
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
DV worked like a charm. Good job. Thanks very much guys.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.