Force Upper Or Proper Case On Entry
Feb 16, 2007
Below is the existing code that I'm working with and would like to be able to make the ' name' column either Upper or Proper case on entry. I haven't decided which I'm going to use yet.
Set r = Sheet1.Range("A2:C65536")
If Not Intersect(Target, r) Is Nothing Then
sTgt = Trim(Target.Value)
If sTgt = "" Then Exit Sub
Select Case Target.Column
Case NmCol
If InStr(sTgt, ",") = 0 Then
iSpc = InStrRev(sTgt, " ")
Target.Value = Mid(sTgt, iSpc + 1) & ", " & Left(sTgt, iSpc - 1)
End If
View 3 Replies
ADVERTISEMENT
Sep 6, 2007
I would like to format a row of cells so that when a word is entered into the cell it automatically becomes a capital.
I need the word to be capitalized so that I can use it in a custom function. The function uses the word from this cell and goes through a bunch of cases in determing how to classify the string.
I think more than one solution is possible and I would greatly appreciate some feed back, I've tried looking into turning all the letters of a string in my VBA code to capitals, or a way to format the cells, so that the string is already capitalized when entered into the VBA code, but I'm still a novice at VBA and unsure on how certain commands work.
here is a sample of my vba code.
Function WeightI(Shape As String, sDim As String, dLenFt As Double) As Double
Const pi As Double = 3.14159265358979
Const Ft2In As Double = 12
Const dDen As Double = 0.2835 ' density of steel, pounds per cubic inch
Dim aiStr() As String ' dimensions as strings
View 5 Replies
View Related
Mar 13, 2014
I have a worksheet which is populated from a macro using the following code.
Code:
Sheets("Create Sub Contractor").Range("B6:B65").Copy
With Sheets("Sub Contractor Information").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
.PasteSpecial (xlValues), Transpose:=True
End With
The problem is that some of what is being copied needs to be Proper and Some Upper, therefore I cant use a paste special option.
What I'd like to be able to do is format the columns in the destination sheet ("Sub Contractor Information") from row 4 down to what ever format they need to be individually as there are some columns that are numbers, some text and numbers........
View 4 Replies
View Related
Mar 12, 2007
I have a series of input boxes and in one particular input box ("TypeScore") if the lower case is entered I want to convert it to upper case. Here is a small snippet of the series of input boxes. I have tried just about every suggestion I can find on this forum, but can't get it to work.
View 9 Replies
View Related
Aug 29, 2007
Is it possible to make a spreadsheet so that everything that is typed into it is in CAPITALS? I have a need for that for a spreadsheet that I am using at work, but I remembered that in the title box above it only capitlaizes the first letter. I thought if it can do it with the first letter can Excel do it with all of the letters.
View 3 Replies
View Related
Mar 6, 2014
I have many shortcut words to make full name using Auto Correct option under Proofing
I am using this code to make force range in Uppercase
[Code] ....
But when i put a auto correction value in small case "ip" {like ( IP = IRFAN PAT )}
Then the cell value is going in uppercase "IP" but auto correction is not working
I want ..if i type in range "ip" result shoul be (" IRFAN PAT ") not "IP"
View 2 Replies
View Related
May 8, 2008
Sub Addy()
Do Until ActiveCell. Offset(0, -4) = ""
Renamer = Proper(ActiveCell)
ActiveCell = Renamer
ActiveCell.Offset(1, 0).Select
Loop
End Sub
fail? Trying to remove all capitals from names/addresses. Error message is "compile error - sub or function not defined"
View 6 Replies
View Related
Aug 26, 2009
I have a string of names that run together without spaces or commas between each name.
"Danny TrejoJean Claude van DammeVincent SchiavelliGabrielle FitzpatrickDavid 'Shark' FralickPat Morita" for example.
Is there a way to add a comma and space between a lower case and upper case letter?
View 7 Replies
View Related
Jul 1, 2006
Convert to Proper UPPER Procedure ...
View 9 Replies
View Related
Jun 20, 2008
When I use a simple formula such as:
=upper(a1)
that will obviously change whatever is in a1 to Upper Case - but it will put it in the cell that holds the formula.
What I want to know is:
Is there any way I can format the cell to run the formula when the information has been pasted into the spreadsheet
View 9 Replies
View Related
Nov 17, 2009
if there's any way for vba to detect if each individual character in a string is in caps, and if so, convert to lower case, and if it's in lower case, convert to capitalized
The text will vary in length and content... so he wants to see how we can change:
"This Is Strange" to "tHIS iS sTRANGE"
"THIS IS STRANGE" to "this is strange"
"this is strange" to "THIS IS STRANGE"
View 2 Replies
View Related
Jun 30, 2013
I've had good success with the following line of code
Code:
Sheets("Test").Range("A2") = UCase(Sheets("Test").Range("A1"))
But how do I do the same, converting the to Proper Case?
View 2 Replies
View Related
Feb 21, 2007
I have just recently found that I can do case correction with Excel but I am manually having to do it how can I add it to my macro? The function for doing it does not seem straight forward to me on putting in macro I am sure it is simple but just missing some element of it.
I need to have Proper case for columns C, G and H from rows 11 and down.
View 9 Replies
View Related
May 30, 2007
how can i change the text put into an input box into proper case regardless of what my user puts in?
View 9 Replies
View Related
Aug 10, 2009
I found this bit of Worksheet_Change code to change the target area to UpperCase. This works fine.
If Not Intersect(Target, Columns(2)) Is Nothing Then
Set rng1 = Intersect(Target, Columns(2))
Set rng2 = Intersect(ActiveSheet.UsedRange, rng1)
For Each cell In rng2
If cell.Formula "" Then
cell.Formula = Format(cell.Formula, ">")
End If
Next cell
End If
I could not find anything telling me what the ">" means. I'm assuming that it is a special symbol/wildcard for UCase in VBA.
My question(s), is there a symbol for ProperCase so I can use the same code, just making it change the Target column to Proper? Also is there a list of the special symbols.
View 9 Replies
View Related
Jun 3, 2006
When I asked this question before, I was looking for a way to automate the exemptions on a UserForm. At that time I realized that automation was not a good choice and went with a CommandButton to turn off the Proper case for that entry. I am now trying to do the same thing on a Worksheet change event using this
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column < 1 Or Target.Column > 9 Then Exit Sub
On Error Goto Errhndl
Application.EnableEvents = False
If Target.Column = 1 Then Target.Value = Application.Proper(Target.Value)
If Target.Column = 2 Then Target.Value = Application.Proper(Target.Value)
If Target.Column = 3 Then Target.Value = UCase(Target.Value)
If Target.Column = 4 And Target.Value > "" Then Target.Value = UCase(Left(Target.Value, 2)) & "-" & Right(Target.Value, 2)
If Target.Column = 8 Then Target.Value = UCase(Target.Value)
If Target.Column = 9 Then Target.Value = UCase(Target.Value)
Application.EnableEvents = True
Exit Sub
Errhndl:
Application.EnableEvents = True
End Sub
My problem is with Target.Column = 1. I need a way to disable the proper case for a single row. I tried to use an additional column (J) and place a x in that row, but I could not figure out how to detect if there was anything in that column for the target row.
View 7 Replies
View Related
Sep 20, 2009
In column J of my Excel 2003 worksheet named: Sheet1, is a list of mailing addresses. A copy of the spreadsheet is attached. I need a macro to please capitalise (Upper-case) the second character only of all the Post Office addresses.
Example:
Po Box 65, ORLANDO, NSW 2697
All of my Post Office addresses are prefixed: Po
I need the Post Office addresses to be prefixed: PO
So, the above mentioned example should look like this: PO Box 65, ORLANDO, NSW 2697
View 4 Replies
View Related
Feb 21, 2012
How can i use UPPER case formula in a list ?
i have a on sheet4 at A1, source of data is sheet2 from A3:A5000. some entries in lower case, i want in list (A1) all entries UPPER case.
View 9 Replies
View Related
Sep 22, 2008
I have this code (this is just a snippet)
For Each cel In Range("E6:AI15").Cells
If IsError(cel.Value) Then
Else
Select Case UCase(cel.Value)
Case "H"
cel.Font.ColorIndex = 0
cel.Interior.ColorIndex = 3
Case "S"
cel.Font.ColorIndex = 0
cel.Interior.ColorIndex = 10
but I want the cells to accept both upper and lower case values and format the cells accordingly ie. H or h, S or s
View 9 Replies
View Related
Feb 24, 2009
I have a usferform where I want the result from textbox1 displayed in the cell as all caps.
here is what I tried but not effective.
If UserForm7.CheckBox5 = True Then Range("A23") = UserForm7.UCase(TextBox1.Value) & " SPECIAL EVENT RENTAL"
View 9 Replies
View Related
Aug 17, 2014
I have been using following code to extract all upper case words in a string but the problem is I can not extract words which are proper. For example
This is GOOD
Present output: GOOD
Desired Output: This GOOD
[Code] ....
What can be suitable modification in this case?
View 8 Replies
View Related
Sep 9, 2009
I have an Excel 2003 worksheet with all the data in it in Upper-case.
I found this VBA code which works if you change a cell.
View 6 Replies
View Related
Dec 10, 2008
I have a number of textboxes into which I enter the surname of individuals ... at present the textboxes are set to store all names in Uppercase. Is there coding to return names beginning Mc... or Mac... ie McClOUD or MacDONALD, in the more recognized format. I am sure this has been included in the forum but could not find it in a search of the site.
View 4 Replies
View Related
Sep 19, 2006
Is it possible to modify this code to exclude the first sheet in the workbook which is called Costs?
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
''''''''''''''''''''''''''''''''''''''''''''
'Forces text to Proper case for the range A14:A39
''''''''''''''''''''''''''''''''''''''''''''
If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub
On Error Resume Next
If Not Intersect(Target, Range("A14:A39")) Is Nothing Then
Application.EnableEvents = False
Target = StrConv(Target, vbProperCase)
Application.EnableEvents = True
End If
On Error Goto 0
End Sub
''''''''''''''''''''''''''''''''''''''''''''
'Forces text to Proper case for the range A14:A39
''''''''''''''''''''''''''''''''''''''''''''
View 3 Replies
View Related
Jan 25, 2007
I am using this macro to ensure that a range of cells appear in Proper Case. However I am encountering a drawback, sometimes I have text which I want in Upper Case but which is changed into Proper Case. I was wondering if there was a way to work around this. Example: Practical W/W appears as W/w or Woodturing (GMC) appears as Woodtrunign(gmc)
Private Sub Worksheet_Change(ByVal Target As Range)
''''''''''''''''''''''''''''''''''''''''''''
'Forces text to Proper case for the range A15:A40
''''''''''''''''''''''''''''''''''''''''''''
If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub
On Error Resume Next
If Not Intersect(Target, Range("A15:A40")) Is Nothing Then
Application.EnableEvents = False
Target = StrConv(Target, vbProperCase)
Application.EnableEvents = True
End If
On Error Goto 0
End Sub
View 2 Replies
View Related
Oct 19, 2007
How can I extend proper() to NOT change "PO Box 333" to "Po Box 333". Ideally, I would like to supply a list of words such as PO and all the 2 letter directionals (NE,NW,SE,SW).
There are also cases such as a last name of MacNamara which should have a capital M and N. Even worse, I see that 3rd becomes 3Rd which is very sad.
I'm assuming the data was supplied in uppercase
View 9 Replies
View Related
Jul 18, 2014
Is there a way of selecting text and converting it all from lower to upper case?
View 3 Replies
View Related
May 19, 2009
I have a column containing 8000 records, some of them are in upper case & rest are in proper case, is there any way wherein i can filter all records which are in upper case without using macros, i mean is it possible to do it using ISNA function ?
View 2 Replies
View Related
Jul 30, 2014
Only have upper case letters.
I am trying to get certain cells to only have upper case letters.
each cell in the code will have a single letter typed in.
I would rather have it as an event code (change while it is typed), but I have not grasped the whole thing yet.
This code does not give me any errors, but it is not changing the lower case letters into upper case letters either.
[Code] ......
View 2 Replies
View Related
Sep 6, 2005
=upper("venkat") returns VENKAT
=upper(a1) returns the text in A1 in upper case
similary lower
proper will turn the first letter into uppercase
is this what you want;.
Terry <terrybetts11138@hotmail.com> wrote in message
news:dbl0dk$poa$1@nwrdmz02.dmz.ncs.ea.ibs-infra.bt.com...
> Win XP Pro
> Office 2003
>
> Using "Excel" and wish to select any text in a worksheet to ALTER the case
> to either upper or lower.
> When using "Word" it is easy via the menu.
> Is there a menu driven option in Excel
of using a function for this purpose.
View 14 Replies
View Related
