Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detect UPPERCASE in cell value?
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detect UPPERCASE in cell value?
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 -- HTH... Jim Thomlinson " wrote: 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detect UPPERCASE in cell value?
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 wrote in message ... 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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detect UPPERCASE in cell value?
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 " wrote: 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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detect UPPERCASE in cell value?
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 "Rick Rothstein (MVP - VB)" wrote in message ... 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 wrote in message ... 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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detect UPPERCASE in cell value?
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 -- HTH... Jim Thomlinson "Jim Thomlinson" wrote: 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 -- HTH... Jim Thomlinson " wrote: 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! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detect UPPERCASE in cell value?
On Jul 23, 12:36*pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote: 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 -- HTH... Jim Thomlinson "Jim Thomlinson" wrote: 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 -- HTH... Jim Thomlinson " wrote: 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!- Hide quoted text - - 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!! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detect UPPERCASE in cell value?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If Cell Contains specific UPPERCASE text | Excel Worksheet Functions | |||
Change cell range to Uppercase | Excel Programming | |||
How to start every word in a cell uppercase? | Excel Discussion (Misc queries) | |||
Format cell to uppercase using code | Excel Programming | |||
VBA Auto Cell Formatting Uppercase | Excel Programming |