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.
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
In my worksheet there are ranges A3:C37, E3:E37, J3:K37 and P3:P37 that all contain text that I would like to automatically change to proper case once the user leaves any of the referenced cells.
I have tried various codes form this forum and searched for hours on the net for a solution to do this but no matter what I do/try nothing works (for long)
Another forum user did help me out with some code but there was an issue with column C, L & O (which are set as drop down lists) and when the code was put into the workbook these columns stopped working and froze the app.
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.
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.
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.
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
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
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.
When using the PROPER function, it capitalizes the first letter in a text string and any other letters in text that follow any character other than a letter, and converts all other letters to lowercase.
However, if A1 contains the text "2-cent's worth"; then =PROPER(A1) will return the following result: "2-Cent'S Worth".
Is there a way to prevent the PROPER function from capitalizing the first letter following the apostrophe?
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
Some handy code that I can put in a VBA module that will convert all text within a Spreadsheet to Proper or Sentence like this ---> Hello Everyone, Hope You Are All Happy.
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........
I have a Excel text document that I compile from multiple sources, and if they don't leave a "white space" before a "/", "-", or "(" the Proper Case command does not capitalize the word that immediately follows that symbol.
The Average of the last 6 items in column A is R112.50
I want a formula that will make the first letter of the sentence in proper case i.e "The average" as well as "R" before the value in proper case i.e R 112.50
I use the applicaiton timer but it said time is 5 so what's the problems? Below i want to change to case construct.
Sub GreetMe() If Time < 0.5 Then MsgBox "Good morning!" & " It’s now " & Time, , “The Time” ElseIf Time >= 0.5 And Time < 0.75 Then MsgBox "Good afternoon!" & " It’s now " & Time, , “The Time” Else MsgBox "Good evening!" & " It’s now " & Time, , “The Time” End If End Sub
this is what i did so far but still having problems with it with case construct
Sub GreetMe() Dim morning As Integer Dim afternoon As String Dim goodevening As Integer Select Case morning Case Application.AutoRecover.Time < 0.5.......................
I needed to change the case of entries in range B8:B100 to uppercase, for which I used the VBA I have been using, and it works fine. I now require to also change the case of ranges C8:C100, D8:D100 and E8:E100 to propercase. So, I changed the script to:
I am trying to change string values of a name from uppercase to lowercase. The data is layed out as lastname then first name. As an example, I have the following values in column C:
ANDERSON MICHAEL SMITH SUSAN P JOHNSON PATRICK JORDAN MICHAEL R WOODS TIGER CLINTON WILLIAM J
I am trying to convert to following (with "," between lastname & firstname):
Anderson, Michael Smith, Susan P Johnson, Patrick Jordan, Michael R Woods, Tiger Clinton, William J
I have a start of how I think the code should flow but can't figure out how to do the conversion.
=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.
find the "add in" that converts the case text from lower to upper in excel. i've already downloaded this before but can't remember where from and it is much easier than creating a formula or a macro
I have found the code below that is very good and changes the case size to Upper, Proper or Lower. The problem is that when I select a cell, range of cells, column or row it changes the entire sheet instead of the selected range.
Code: Sub CaseSize() Dim myCase, rng As Range, r As Range myCase = Application.InputBox("Enter" & vbLf & "1 for Upper Case" & vbLf & _ "2 for Lower Case" & vbLf & "3 for Proper Case", Type:=1) If (myCase = False) + (Not myCase Like "[1-3]") Then Exit Sub On Error Resume Next
