Exception To Forcing Text To Upper
Oct 20, 2008
I am using the below code to force a range of cells to change to Upper Case, what i am after is an exception to the rule for example the words Pick Up i don't want to force to upper case, is there VBA i can add to the below to achieve this?
Private Sub Worksheet_Change(ByVal Target As Range)
''''''''''''''''''''''
'Forces text to UPPER
''''''''''''''''''''''
If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub
On Error Resume Next
If Not Intersect(Target, Range("w6:w299")) Is Nothing Then
Application.EnableEvents = False
Target = UCase(Target)
Application.EnableEvents = True
End If
On Error GoTo 0
End Sub
View 9 Replies
ADVERTISEMENT
Mar 7, 2012
Is there Code for forcing all text in a certain range to Uppercase?
Example: A1:E20
if possible I need the code to be just inserted into the sheet code. Maybe were when the user moves to another cell it changes them then.
View 2 Replies
View Related
Jun 4, 2008
I am wondering if there is any way to "force" a number to have three decimal places instead of two and still have that number valued as a number for use in formulas later (like AVERAGE, etc.).
What I have is a combination of HLOOKUPs, IF statements, and Conditional Formatting.
I have a Master Grades sheet and individual Class sheets. I use an HLOOKUP, matching a Student Number in both sheets, and looking in the Class sheet for the P/F indicator. If the indicator is a F, the HLOOKUP is to return the Final Grade from the Class sheet and input that into the Master Grades sheet, forced to three decimal places (whereas the numbers in the Class sheets are at two decimal places) If the indicator is P, the HLOOKUP simply returns the Final Grade from the Class sheet and inputs it into the Master Grades sheet with two decimal places.
I have the failure grades forced to three decimal places so that the Conditional Formatting in the Master Grades sheet highlights the failed grades for teachers and anyone else can see right away that a student failed, but the number still allows the Master Grades sheet to calculate the overall AVERAGE for the student as well as the AVERAGE for that class.
If any of this is not quite clear, please ask and I can further expand on what I am doing.
Here is the code I have right now that is not working correctly (and I know why it's not): ....
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
Oct 14, 2009
I have data in cell A1 as below:
36, TREE ROAD, 5TH FLOOR
I want it in proper format in cell A2 as below:
36, Tree Road, 5th Floor
I used formula =Proper(A1), it gave results as:
36, Tree Road, 5Th Floor
The only problem I face is after any number, the next character should in lowercase, i.e. 5th
View 9 Replies
View Related
Dec 3, 2004
I would like a user to be able to enter text into a cell in any case and have it be displayed in all capital letters (or as a "Proper" capitalization). Seems strange that >FORMAT doesn't seem to have a "case" selection.
Past threads show VB code to do this, but I don't want the user to have to click a button to change case. Could the VB code run automatically after the user hits >enter, or tab, or an ->? Is there a way to >FORMAT a cell to do this change? I know the "Upper" & "Proper" functions but it would be very clumsy to have the user enter data in cell A1 just to be displayed in capitals in cell A2.
Example: When user types "lax" in cell A1, I would like "LAX" to be displayed in cell A1.
View 8 Replies
View Related
Jan 28, 2014
I'm trying to formulate:
If it's mixed case, copy it. If it's all lower case or ALL CAPS, then make it PROPER.
i.e., if 'bananas' is in A1 and B2 says =PROPER(A2), then B2 says Bananas, because PROPER capitalises the first letter of each word.
I would like to do
[Code] .....
View 5 Replies
View Related
Jul 11, 2012
I have a workbook on which I want to change all the text from Lower to Upper case.
How can I do this without retyping .
View 4 Replies
View Related
Aug 8, 2008
I have created a text convertor which will convert any text into upper case or lowercase (not very complex but very handy!) Is there anyway way that when text is in uppercase and I convert it to lower case that it makes every character after a full stop in upper case
Example
DAVID WAS NOT WELL. HE WENT HOME
to
David was not well. He went home
I currently get
david was not well. he went home.
View 9 Replies
View Related
Jan 13, 2009
way to split a text line into seperate cells using upper case letter, or any letter for that matter, as delineator. A couple of sample lines would be as follows. It happens to be in upper case, but doesn't need to be, so any alphabetical letter would do, I think.....
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
Sep 10, 2008
I am using the Find fuction to look for values in cells that contain text in both lower and upper case. I am comparing the values that I have stored in a lookup table. I am running into trouble when the lookup table doesn't match the text cells....
View 9 Replies
View Related
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 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
Oct 2, 2009
I have code that selects chart source data by searching the header row for dataTyp (in this case the dataTyp will be set to "IMP"). The target header cells look like "IMP 100Hz", "IMP 400Hz", "CAP 100Hz", "CAP 400Hz" etc. recently I inserted an averaging column at the end of each group of data columns. The headings for the new columns look like: "IMP AVG" "CAP AVG" etc. I need to exclude the ...AVG column from the source data selection. Code snip:
View 2 Replies
View Related
Apr 11, 2009
I attached the extract. I am having the issue in the Total Workbook. Specifically, the Tax and Debit Column. If a company is on both workbooks (Insider Processor, Outsider Processor). The Tax and Debit should be charged once in the Total Workbook. But i don’t know how to get this happening
View 6 Replies
View Related
May 17, 2006
I am trying to get 2 different formulas to run depending on the value of the 2 cells.
Example: if cell "A" is equal to cell "B" then one formula, if it is different than a second formula.
I hope I did this right, if not please let me know and I will ask the question in a different manner.
View 7 Replies
View Related
Oct 19, 2006
I have a workbook setup to give me the nutritional break down of all of the foods I consume. I can get the subtotal of each day. What I need is to also get the subtotal of each meal. I can't seem to get this meal subtotal without adding it to the daily total twice (once in the listing and once in the subtotal). I was thinking of trying to add an exception to the sumif formula in columns I, K & M but I don't know if that is possible. I have included a cut down version of my workbook to illistrate the problem. I will gladly supply a full vertion to anyone who would like a copy for thier own use.
View 5 Replies
View Related
Feb 27, 2007
I am trying to write a macro that looks at columns C-I in row 6, to check if the value is G, if it is then I want it to delete C5,6,7 & 8 and so on along to coumn I. So all that I am left with at the end of the macro are the values that weren't green without any blank gaps in the columns. I started using the following
If C6 = G Then
Range("C5:C8").Select
Selection.Delete Shift:=xlToLeft
End If
And using that same code for D6, E6 etc etc, but obviously, when it deletes the cells it shifts them left so the columns change values, so if C6 is equal to G then it deletes it but then what was D6 becomes C6.
View 2 Replies
View Related
Feb 15, 2014
If Cell value in "C36" or "D36" is Yes, then a picture will be displayed (either Picture 1 or Picture 2). If Cell value is No, then the pictures won't be displayed.
The code I use:
[Code] .....
By default, all pictures on the Excel sheet are invisible.
My problem is, there are 3 pictures I'd like to display at all time because they are part of the document's decoration.
Therefore, I'd like to add an exception for the Picture A, Picture B, Picture C so they are not affected by the command "Me.Pictures.Visible = False".
View 3 Replies
View Related
Jul 6, 2014
I have a big Excel file that is supposed to be used by many users and therefore I need some cells locked. But problem is I use these cells in my vba codes which the users must be able to run by using the buttoms in the file. But now I get error in every macro since the cells the code are trying the reach is locked. Can I make an exception for vba codes in some way so that I can run my macros?
View 4 Replies
View Related
Mar 25, 2014
why a macro that works without a problem in Excel 2010 now crashes Excel 2013. The error produced says 'Automation Error Exception Occurred'. If I place a stop in the code and hold the F8 key down, the code runs perfectly (Excel 2013) but if I run it, it will crash. The code is copying a worksheet into a new workbook, adding 2 more worksheets, saving then emailing the workbook before closing. It goes through the routine about 7 times before crashing (sending only 7 email out). It's something to do with the email because I can bypass that code and it runs that part without a problem.
Our office just upgraded all the computers to Office 2013 and I've got to get this macro running again. I had to email 100 reports one-by-one today and I didn't like it.
View 6 Replies
View Related
Mar 25, 2014
Currently i am using index and match in excel but however i am unable to get the result tt I want.. what i need is
Data to check with item master whether all 3 (article, color code and size) matches and if it doesn't match, which of the field is wrong. (eg: wrong color code, wrong size or article don't exist).
[Code] .......
Above is my item master
Data to check
[Code] ........
Example case: 1
Checking first row of data check
Article number: 2-50660147
Color code: 6
Size: M
Result: "Wrong size"
But however, it returns as wrong size as it only reads the first time the article number appear in the item master and does not recognize the second time the article number appears with the correct color and size.
View 9 Replies
View Related
Oct 8, 2008
Is there a data validation that will not allow all upper case or all lower case characters?
For examples:
Allow:
John Doe
101A N Woodbine Street
Disallow:
JOHN DOE
101A N WOODBINE STREET
Disallow:
john doe
101a n woodbine street
Or perhaps formatting that would convert the words to upper/lower case?
Or, I would be interested in VBA if it is the only way.
View 9 Replies
View Related
Jul 16, 2009
I have the following formula:
View 2 Replies
View Related
Feb 26, 2009
Command button opens userform with a listbox. Unfortunately during testing I found that if a value is not selected from the listbox and the OK button is clicked, it bugs out.
Is there a way to force a cell value from the listbox? BTW, the listbox first number is 3 so it would be my choice of default.
I would imagine the code would need to be with the OK button, so here is the code for it:
View 7 Replies
View Related
Jul 30, 2008
I'm importing data from CMS into excel and I then have several other sheets that harvest the raw data from the import and use it to calculate various things.
My problem is that when I import time data, if it is less than 1 hour (eg 39mins, 12 secs) it will format as :39:12 rather than 00:39:12 and for some reason the calculations in other sheets omit any data that does not contain the hours field.
I have tried formatting the cells as hh:mm:ss but this does not force inclusion of the hours field.
I guess what I'm asking is:
1. Is there a way of making excel include both data in the hh:mm:ss format and :mm:ss format in the calculation?
or
2. Is there a way of forcing data in the :mm:ss format to include an hours field populated by zeros?
View 7 Replies
View Related
Dec 6, 2013
I want to have a fullscreen application all the time, so for this I've added to things in my code:
1) Application.DisplayFullScreen = True in Workbook_Open
2) Same in WorkBook_WindowsResize
I open my workbook, it goes fullscreen. Until now all OK. However, if I press ESC or doubleclick on the topbar, it goes out of it.
Now the weird thing: if I click the "Restore window" button (which is right under the application maximize button that incidentally I have disabled) it snaps on fullscreen and THEN it keeps this state forever, even on ESC/doubleclick. Which is what I want.
So my question is: what does exiting from fullscreen and going back in change so that the workbook event starts triggering, and how I can replicate it programmatically? I've already tried WindowState = xlNormal followed by xlMaximize, it doesn't change anything.
View 3 Replies
View Related
Oct 21, 2008
recently excel 2003 began forcing the save as dialogue box when i try to close the workbook. I do not remember installing anything in particular on my machine when it started acting up.
I tried uninstalling office with Revo Uninstaller (gets rid of those pesky registry entries that the control panel add/remove leaves behind).
I reinstalled and I have the same issue.
Im thinking it is a macro of some sort as when I open the program holding down shift, it does not prompt me to save as when I exit. I do have one excel add in installed, but I have used it for many years without any issues.
View 9 Replies
View Related
Mar 16, 2009
I have created a scorecard and I have certain fields which i need completed prior to the coach submitting the scorecard.
Is there a way which i can prompt the coach if they miss a field
The fields i want completed are:
X17, Y17, Z17, AA17, AB17, AC17, AD17 and AF17
View 9 Replies
View Related