Force Uppercase?

Is there a fairly straightforward way to format cells to in order to 
force uppercase, like you can in a Visual Basic text box, i.e., the user 
types "c" and it's changed to "C"?

0
10/13/2003 8:14:19 PM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
272 Views

Similar Articles

[PageSpeed] 27

You can't use formatting, but you can use an event macro. One way:

Put this in the worksheet code module (right-click on the worksheet 
tab, choose View Code, paste the code in the window that  opens, 
then click the XL icon on the toolbar to return to XL):

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
        Dim cell As Range
        Application.EnableEvents = False
        For Each cell In Target
            With cell
                If Not .HasFormula Then _
                    .Value = UCase(.Value)
            End With
        Next cell
        Application.EnableEvents = True
    End Sub

This allows lowercase in formulas, but not direct entries.

In article <3F8B079B.2060904@here.org>,
 bonehead <sendmenospam@here.org> wrote:

> Is there a fairly straightforward way to format cells to in order to 
> force uppercase, like you can in a Visual Basic text box, i.e., the user 
> types "c" and it's changed to "C"?
>
0
jemcgimpsey (6723)
10/13/2003 8:56:07 PM
See http://www.cpearson.com/excel/case.htm

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("A1:A10")) Is Nothing Then
    Target(1).Value = UCase(Target(1).Value)
End If
Application.EnableEvents = True
End Sub

This will automatically change the case of the data when the user enters data in the range A1:A10.
Change this range to the range you need to use for your application




-- 
Regards Ron de Bruin
(Win XP Pro SP-1  XL2002 SP-2)
www.rondebruin.nl



"bonehead" <sendmenospam@here.org> wrote in message news:3F8B079B.2060904@here.org...
> Is there a fairly straightforward way to format cells to in order to
> force uppercase, like you can in a Visual Basic text box, i.e., the user
> types "c" and it's changed to "C"?
>


0
rondebruin (3789)
10/13/2003 8:59:56 PM
Hi

Not just like that, a cell does not trig any events or run any code until the entry is
totally completed. But then; paste in the worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Target.Formula = UCase$(Target.Formula)
Application.EnableEvents = True
End Sub

Refint this with tests for target.count (=lots of cells pasted) and similar to suit your
needs.
--
HTH. Best wishes Harald
Followup to newsgroup only please.

"bonehead" <sendmenospam@here.org> wrote in message news:3F8B079B.2060904@here.org...
> Is there a fairly straightforward way to format cells to in order to
> force uppercase, like you can in a Visual Basic text box, i.e., the user
> types "c" and it's changed to "C"?
>


0
innocent (844)
10/13/2003 9:10:33 PM
> If Not Application.Intersect(Target, Range("A1:A10")) Is Nothing Then
>     Target(1).Value = UCase(Target(1).Value)
(...)
> This will automatically change the case of the data

.... and also formulas to static values.. Be careful with those .value things ;-)

Best wishes Harald
Followup to newsgroup only please.



0
innocent (844)
10/13/2003 9:22:52 PM
Yes Chip must update his page<g>

-- 
Regards Ron de Bruin
(Win XP Pro SP-1  XL2002 SP-2)
www.rondebruin.nl



"Harald Staff" <innocent@enron.invalid> wrote in message news:%239a78%23ckDHA.1488@TK2MSFTNGP12.phx.gbl...
> > If Not Application.Intersect(Target, Range("A1:A10")) Is Nothing Then
> >     Target(1).Value = UCase(Target(1).Value)
> (...)
> > This will automatically change the case of the data
>
> ... and also formulas to static values.. Be careful with those .value things ;-)
>
> Best wishes Harald
> Followup to newsgroup only please.
>
>
>


0
rondebruin (3789)
10/13/2003 9:25:40 PM
Ron de Bruin wrote:
> See http://www.cpearson.com/excel/case.htm
> 
> 1 Private Sub Worksheet_Change(ByVal Target As Range)
> 2 Application.EnableEvents = False
> 3 If Not Application.Intersect(Target, Range("A1:A10")) Is Nothing Then
> 4     Target(1).Value = UCase(Target(1).Value)
> 5 End If
> 6 Application.EnableEvents = True
> 7 End Sub

Many thanks to Ron and all others who replied. And I've also taken the 
cue and done a Google search and found several useful sites for future 
reference.

I set up my worksheet to handle several discontiguous ranges by using a 
succession of IfElse statements, but I wonder if I could just modify the 
Range() argument in Line 7 instead? For example, can I name a set of 
discontiguous ranges and then pass that range name to the Range() argument?

0
10/14/2003 11:15:59 PM
Reply:

Similar Artilces:

Force Uninstall
All, A coupla questions . . . Suppose I just want to forcefully uninstall Exchange 2000 from a server, and an org. Currently the uninstall fails because (as mentioned in a previous post) I have users that still show as being on that server, however the store that those users were on is no longer there (because of a disaster). Is there some way to force the uninstall? Another question. I have two healthy clustered machines running currently. Suppose I just remove the object for the old exchange server from the ESM? Assuming I have performed all of the other steps steps (like moving o...

forcing user input into cells
howdy all... i'm new to doing stuff with excel have a bit of a background in javascript programming what i'm trying to do is force a user to enter something, anything int a specific cell if the user tries to tab past the cell and leave it blank, i want a error to come up, telling them that they have to enter something befor they can continue on with entry into other cells any help would be much appreciated -Thank -- georgi ----------------------------------------------------------------------- georgio's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=164...

Force quit
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Closing Word get error message &quot;A file error has occurred. Check your network connections or make sure the disk is properly inserted&quot; Am then required to use force quit. Anybody have solution? Make sure Office & OS X are fully updated. If they are & you still encounter the problem, crashing on Quit is usually the result of one of theses causes: Need to repair disk permissions Damaged preference files Corrupt Normal.dotm template See this page for the respective links in t...

Forcing Appointments
I use outlook to schedule appointments with my customers with specific employees. I have a scheduling department that does this scheduling via outlook. The employees have no option to reject or modify these appointments; therefore, I would like to force this on their calendar with them having to accept it and not allowing them to modify it. Additionally, I need to know real time once they are scheduled. Right now I don't know they are scheduled until they accept and that sometimes takes too long and then they are inadvertently double booked by the scheduling department. So how do ...

Force REPLY to go back to distribution list
I have an issue whereby a department is insisting that they want their mailing lists marked so that any time anyone clicks REPLY (not "REPLY ALL" - they refuse that as an acceptable solution, it must be REPLY) that the reply message goes back to the distribution list and not the message originator. They further insist that this capability must occur on the mail server (and not on Outlookclient side), since they use a multitude of different mail clients (many on Unix/Linux) and therefore it is a server-side responsibility to make this happen. They say that this capability exists in ...

Script to make text in uppercase, on workbook level, but not for other opened workbooks.
Hi, (A) I do have undermentioned script, but I cant't get it worked. I want to have cells changed to uppercase after Enter-command. (B) How to have this script for all sheets, so to have it on workbook level? And, how to avoid that other excel documents that are opened also have the text capitalized? Thank you. Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo Error_handler With Target If Not .HasFormula Then Application.EnableEvents = False If Target.Row = 10 Then Target.Value = UCase(Target.Value) Target.Value = UCase(Target.Value) Application.EnableEv...

force
...

Forcing an account
Hi All Is there anyway to get Outlook 2002 and/or 2003 to force to use a certain mail account for certain contacts? For example for contact A, B and C I want to always send new messages, replies and forwards via mail account 1 and for contact D, E and F I want to do the same using mail account 2. I don't want this to deviate even if one of these contacts has sent an email to an alternate mail account. Even though I set my main account as the default, when certain emails come in on my other accounts I can't force my replies or forwards to go to a certain account. Are there any hidd...

Force PasteSpecialValues in Excel 2007
There was a very nice piece of code written by Aaron Bush (Oorang on MrExcel) in 2007 which disabled cut and copy execution in excel, forcing the copy to copy only values but maintaining the ability to undo (http://www.vbaexpress.com/kb/getarticle.php?kb_id=957). Unfortunately, this code fails in several ways in Excel 2007. 1/ It does not disable ribbon cut/copy/paste features 2/ Paste still functions normally when using <enter> to paste the previously copied cell 3/ If you are copying from a locked cell in a protected sheet, the code generates a 1004 error (though the p...

Forcing an ActiveX control to serialize it's properties.
Is there a way to force an activeX control to serialize it's properties? Kurt "Kurt" <k_nojunk@larimore.net> wrote in message news:OqS15%23p3DHA.3224@tk2msftngp13.phx.gbl... > Is there a way to force an activeX control to serialize it's properties? > > Kurt > > Kurt, Try getting a pointer to the IPersistStorage interface via QueryInterface. Then call the method IPersistStorage::Save which needs an IStorage* to specify where the properties will be saved to. ...

How force table update when no bound field updated?
I have a main form with a 'Balance Due' field. There are two balance due controls, one unbound (displayed) and one bound (hidden). I also have a subform which is a continuous form containing payment data. When payment details are entered or changed, the balance due on the main form and in the bound table needs to be updated. The total of payments is summed in the subform footer and the balance due (unbound control) refers to this subform total. In main Form BeforeUpdate, I copy the unbound balance due to the bound balance due. The problem is that when only payment data is...

Force Secure
Is there any way to force the system to secure itself after a cashier uses the time clock? Great Question. But No. "Scott S" wrote: > Is there any way to force the system to secure itself after a cashier uses > the time clock? > > > ...

Creating an event by force.
Hello, I want to create an event by force. How can I do that ? (for example : ToolStrip.ItemClicked event) Thanks :) To the dark side this path leads. Explain your request you should. Yoda On 6/10/2010 6:06 PM, Mr. X. wrote: > Hello, > I want to create an event by force. > How can I do that ? > > (for example : ToolStrip.ItemClicked event) > > Thanks :) Do you mean SomeToolstrip.Items (0).PerformClick()? -- Mike Thanks, specific, this help. I need more generic solution (I would like to do any event, not only the PerformClick event). ...

Possible to force output format of empty elements?
I have built a small integration app using VS .NET 2003 that extracts orderinformation from a 'webshop'. Extracting the orderinformation works fine. Appending the order elements in the XmlDocument was also done in a jiffy. The final step is to save the document to disk and then ship it to another system using ftp. The xml orderfile produced must fit a set specification of the recieving system. That specification states that empty elements must be formated in one line using a long format for empty elements. It should look like this: <address1></address1> And not like...

Force combo box selection before moving off field
I've searched through the group but I can't seem to find exactly what I'm looking for. Using 2000 ... I have a combo box on a form. The row source is a simple query. The control source is a long integer (link to an autonumber). Form is based on a table. I don't want the user to be able to move off the CB (combo box) unless they select something from the list (or type in a new item). I've tried making the control source required in the table. This won't let them move off the RECORD until they do something in the CB. I want them stopped at the CB. I set up...

Forced quit
Version: 2008 Operating System: Mac OS X 10.1 (Puma) Processor: Intel Frequently excel freezes and I must use force quit. The message is displayed that auto recover could no longer save my information. Having to force quit frequently is very annoying. I would be willing to turn off autosave if this would solve the problem. Are there other solutions? What should I do? ...

Forcing Outbound SMTP to bind to a particular address
This is likely addressed elsewhere, but for the life of me i can't find it. We are running an Exchange 2003 active/passive cluster. Inbound SMTP works great, but outbound SMTP is binding to the wrong IP address. Instead of binding to the virtual IP, it binds to the machine IP. The virtual IP address is set up to be natted correctly, but the individual machines are hide-natted using a different public IP address, which isn't listed in DNS. Is there a way to force the Source IP address on outbound SMTP connections? Is this clear as mud? Thanks in advance, Earl You should have...

Excel SaveAs to force overwrite of extant file
I'm using Excel 2007 in 2003 compatibility mode. I have a VBA macro that saves a dynamic file name using SaveAs, but the file name may or may not already exist. I wish to force the SaveAs command to overwrite any pre-existing file automatically (without user intervention to confirm that I wish to overwrite the extant file). I'm sure I could do this in Excel 2003 but can I do it in Excel 2007 and in compatibility mode? Wrap your code in Application.DisplayAlerts = False your save code Application.DisplayAlerts = True Gord Dibben MS Excel MVP On Fri,...

Forced Upgrades
I just wanted to say I don't appreciate Microsoft pulling the plug on my Internet commection to Money 2002. It took Microsoft three years to get that one working the way it was designed. I don't upgrade because I'm tired of the bug fixes that are needed to make it work. I was forced to sign up for Money Premium 2008. I lost a week's worth of financial data when I loaded a backup because no one notified me they were pulling the plug. Since then, my computer has crashed five times, it refused to allow me to manage online accounts and ``reenter'' my information beca...

Force Recipient
I'm setting-up a training room with 8 PCs. I'd like all email sent by the students to always be sent to the instructor's email address, regardless of what email address the student uses. Is there a way to force Outlook 2002 to do this? Or, can it be done with Exchange? Thanks! Ted ...

Force underlining of menu access keys.
How can you over-ride the Control Panel setting 'Display->Appearance->Effects->Hide underlined letters for keyboard navigation until I press Alt key', so that the access keys are always underlined. It must be possible, because the Visual Studio 6 IDE and Firefox both seem to ignore this setting. I know that disregarding the users wishes is potentially a gray area, but when the check-box is cleared, it always re-checks itself after a few days (possibly by our IT departments profile). Either way, I think it's more useful to always the display the underline for our MFC app...

Forcing SFO in Offline
Is there a Reg Key that tells Outlook that CRM is offline. I am trying to start outlook with CRM in Offline Mode. I am having some problems getting it to go offline Thanks HKEY_CURRENT_USER\SOFTWARE\Microsoft\MSCRMClient\RCOffline If this is set to 1 you are offline ============================== John O'Donnell Microsoft CRM MVP http://www.crowecrm.com "InFiNiTeX" <jklaws@gmail.com> wrote in message news:1179777057.211018.294960@x18g2000prd.googlegroups.com... > Is there a Reg Key that tells Outlook that CRM is offline. I am trying > to start outlook with CRM ...

Forcing outbound mail through Exch 2003
Hello I'm in the final stages of my Exch5.5 -> Exch2003 migration. Right now, all mail inbound from the Internet is flowing through Exch2003 (as it should). However, all of the mail sent from my network to the Internet is still flowing out through my old Exch5.5 server. How do I configure my Exch2003 server to send its outbound email directly to the Internet rather than sending it to the 5.5 server first? Any help is greatly appreciated. -Steve Basically you need to delete the IMS on the Exchange 5.5 server and ensure that your E2k3 server is configured to send outbound SM...

Printing Reports: Force A4 Format 07-23-07
hello! i am having trouble forcing a report to be in the A4 format when sending to the printer. it happens all the time that the report get send with the format 'letter' which causes the printer to stop and request for paper in letter format. the report i generate totally fits in the A4 format but it won't work the way i want it to! this is pretty annoying because i always have to manually push a button on the printer so that the report can be printed. is there a way through any preferences or vba code to make this work ? maybe someone can help me or give me a hint. thanks in adv...

Forced to Upgrade!
Today I opened Money 2006 and tried to download my checking and savings account data. When I get this stupid banner that I must upgrade. F*$% You! I will not, this is a function of your software like printing! So I will upgrade to QUICKEN!!! Okay! A$$%*^%'s! Please tell us your data migration strategy. Oh, and Quicken enforces time limits on online services as well. If they didn't do it, Money wouldn't do it. "Jerry" <Jerry@discussions.microsoft.com> wrote in message news:C311AA9A-4B3C-4FFF-9D42-B7D26F2A009F@microsoft.com... > Today I opened Money 2006 a...