Detect UPPERCASE in cell value?

[Internetdomainowner]

Fellow programmers... below is some code I tried to create to detect
if the contents of the ActiveCell.Value was all UPPERCASE. The
spreadsheet is importing data and if a cell's value is all updercase
it means something specific with the data that being imported. Any
ideas as to how to make a TRUE & FALSE statement based on the
characters being all uppdercase?

Declare Function IsCharUpper Lib "user32" Alias "IsCharUpperA" _
(ByVal cChar As Byte) As Long
Sub Button22221_Click()

If IsCharUpper(ActiveCell.Value) = True Then
MsgBox "It is bitch.", vbOKOnly, "-Test-"
End If

If IsCharUpper(ActiveCell.Value) = False Then
MsgBox "It's not uppercase.", vbOKOnly, "-Test 2-"
End If

End Sub


Thanks in advance!

 

[Jim Thomlinson]

Give this a try...

Public Function IsUpper(ByVal cell As Range) As Boolean
If cell.Value = UCase(cell.Value) Then
IsUpper = True
Else
IsUpper = False
End If
End Function

 

[Rick Rothstein \(MVP - VB\)]

Your request is not totally clear. What if the contents of the cell have
numbers or punctuation marks included with its all uppercase letters...
would you want TRUE or FALSE for that condition? Also, do you really want a
VB coded function or would a worksheet formula be acceptable?

Rick

 

[Office_Novice]

Option Explicit
Sub Do_Uppercase()
Dim UpperCase

UpperCase = UCase(ActiveCell.Value)
If ActiveCell.Value = UpperCase Then
MsgBox "Ding", vbOKOnly
ElseIf ActiveCell.Value <> UpperCase Then
MsgBox "Nope", vbCritical
End If
End Sub

 

[Rick Rothstein \(MVP - VB\)]

Depending on your answer to my question AND assuming you want a VB function,
one of these should work for you. The first one reports True if Text is
composed **only** of letters and those letters are all uppercase; the second
allows Text to have non-letters as part of it and reports True as long as
the letter parts are uppercase...

Function IsUppercasedLettersOnly(Text As String) As Boolean
IsUppercasedLettersOnly = Not Text Like "*[!A-Z]*"
End Function

Function IsLetterPartUppercased(Text As String) As Boolean
IsLetterPartUppercased = Not Text Like "*[a-z]*"
End Function

If you would want a worksheet formula solution, you will still need to
answer my first question.

Rick

 

[Jim Thomlinson]

Looking at Rick's code makes me think mine is just a tad bloated. Here is a
shorter version of mine...

Public Function IsUpper(ByVal cell As Range) As Boolean
IsUpper = cell.Value = UCase(cell.Value)
End Function

 

[Internetdomainowner]

Looking at Rick's code makes me think mine is just a tad bloated. Here isa
shorter version of mine...

Public Function IsUpper(ByVal cell As Range) As Boolean
    IsUpper = cell.Value = UCase(cell.Value)
End Function
--
HTH...

Jim Thomlinson








- Show quoted text -

Thank you everyone -- you all have great solutions!!!

Special thanks to Rick Rothstein (MVP - VB) who helped me figure out
how to finally use wildcard characters correctly!!!

'========== WILDCARD SEARCH
Function WildCard(Text As String) As Boolean
WildCard = Text Like "*" & UserForm1.TextBox1.Value & "*"
End Function

'-------------------- USERFORM SEARCH BUTTON
Private Sub CommandButton1_Click()
Range("C2:C12").FormulaR1C1 = "=WildCard(RC[-2])"
Application.Calculate
End Sub

Rick, I can't tell you how long I've been searching for code to do
exactly what this does here... This just made all the really advanced
programs I design even better!!

 

[Rick Rothstein \(MVP - VB\)]

Special thanks to Rick Rothstein (MVP - VB) who helped me

figure out how to finally use wildcard characters correctly!!!

'========== WILDCARD SEARCH
Function WildCard(Text As String) As Boolean
WildCard = Text Like "*" & UserForm1.TextBox1.Value & "*"
End Function

'-------------------- USERFORM SEARCH BUTTON
Private Sub CommandButton1_Click()
Range("C2:C12").FormulaR1C1 = "=WildCard(RC[-2])"
Application.Calculate
End Sub

Rick, I can't tell you how long I've been searching for code to
do exactly what this does here... This just made all the really
advanced programs I design even better!!

I'm glad you were able to get something out of my posted code. Just to note,
however, that your WildCard function performs a case-sensitive search. If
you wanted it to be case-insensitive, you would have to apply the either the
UCase or the LCase function to both the Text argument and the TextBox value
in the Like comparison. You might find the function more flexible in this
regard...

Function WildCard(Text As String, Optional _
CaseSensitive As Boolean = True) As Boolean
WildCard = InStr(1, UserForm1.TextBox1.Value, _
Text, Abs(CaseSensitive)) > 0
End Function

The default is to do a case-sensitive search as is done now; but if you
supply False (or 0) for the optional 2nd argument, then the search will be
performed case-insensitive. Also note that I did not use the Like operator
for this function... its main strength is when you want to look into
specific characters rather than testing the whole text (not that it can't be
used that way, but the InStr function is marginally faster).

Rick