Allow times OR certain text entries

G

Guest

I have a range of cells that use the Time Quick Entry method from Chip
Pearson's website. I want to allow specific text entries ("DQ" or "DNS") but
I don't want to allow any other text entries. I added the following code to
allow the text, and convert it to all caps, but I'm not sure how to prevent
other text entries?

If Application.WorksheetFunction.IsText(Target.Value) = True Then
Application.EnableEvents = False
Target.Value = StrConv(Target.Value, vbUpperCase)
Application.EnableEvents = True
Exit Sub
End If

Thanks,
~ Horatio
 
T

Trevor Shuttleworth

Something like:

Private Sub Worksheet_Change(ByVal Target As Range)
If Application.WorksheetFunction.IsText(Target.Value) = True Then
Application.EnableEvents = False
Target.Value = StrConv(Target.Value, vbUpperCase)
If Target.Value <> "DQ" And Target.Value <> "DNS" Then
Target.Value = ""
End If
Application.EnableEvents = True
Exit Sub
End If
End Sub

Regards

Trevor
 
G

Guest

That is exactly what I was thinking of. I just wasn't sure exactly how to
code it. Thanks!
~ Horatio
 
T

Trevor Shuttleworth

You're welcome. Thanks for the feedback.


Horatio J. Bilge said:
That is exactly what I was thinking of. I just wasn't sure exactly how to
code it. Thanks!
~ Horatio
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Trying to run excel vba code when cell value changes from empty to entered value. 1
Copying Cell Conflict with Worksheet Change to UpperCase 4
running change events to macros 8
Concert number to time 1
time quick entry 10
Script to make text in uppercase, on workbook level, but not for other opened workbooks. 8
How do I make specific columns Proper or Caps? 2
Convert list to UPPER, lower & Proper cases. 21

Top