Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Forcing All Caps in input cells
I have a data entry spreadsheet that will be used by multiple employees. For ease of viewing later, I want to ensure that all data entered into the input cells are in all caps. Is there a way to force cells to display all caps at the time of input? If not, is there a way in VBA that I can create a macro that will change the case of the contents of these cell? -- Slave2Six ------------------------------------------------------------------------ Slave2Six's Profile: http://www.excelforum.com/member.php...o&userid=36904 View this thread: http://www.excelforum.com/showthread...hreadid=566195 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Forcing All Caps in input cells
Slave2Six wrote:
Is there a way to force cells to display all caps at the time of input? Hi Slave2Six, This worksheet Change event procedure automatically changes inputs of the range A1:A10 to capitals. Just edit the "$A$1:$A$10" part to suit your needs... Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, _ Range("$A$1:$A$10")) Is Nothing Then Application.EnableEvents = False Target.Value = UCase(Target.Value) End If Application.EnableEvents = True End Sub to get the code in place... 1. Copy it 2. Right click your sheet's sheet tab then select "View Code" from the popup menu 3. Paste the code into the code module that appears 4. Press Alt + F11 to get back to the usual Excel interface 5. If this is your workbook's first macro and if the security level is not set on medium then go Tools|Macro|Security...select Medium|Save|Close|Open...Click on "Enable Macros" on the "Security Warning" dialog that appears each time you open the workbook. Ken Johnson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Forcing All Caps in input cells
Ken, Thanks so much! That's exactly what I needed. -Slave -- Slave2Six ------------------------------------------------------------------------ Slave2Six's Profile: http://www.excelforum.com/member.php...o&userid=36904 View this thread: http://www.excelforum.com/showthread...hreadid=566195 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Forcing All Caps in input cells
Ken, The addition of this code has introduced a new challenge. In the attached document, there is a VLOOKUP function that references the "Product ID" columns. I used Range("$A$1:$EA$178") in the script that you gave me. However, if I delete anything out of the Product ID columns I now get a runtime error. In reality, the only columns that I am concerned about are B, BC, and DD. Any sugestions? -- Slave2Six ------------------------------------------------------------------------ Slave2Six's Profile: http://www.excelforum.com/member.php...o&userid=36904 View this thread: http://www.excelforum.com/showthread...hreadid=566195 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Forcing All Caps in input cells
Slave2Six wrote: Ken, The addition of this code has introduced a new challenge. In the attached document, there is a VLOOKUP function that references the "Product ID" columns. I used Range("$A$1:$EA$178") in the script that you gave me. However, if I delete anything out of the Product ID columns I now get a runtime error. In reality, the only columns that I am concerned about are B, BC, and DD. Any sugestions? Hi Slave2Six, I thinks its a harmless error caused by you selecting then deleting a range of more than 1 cell, Excel can't work with the values of more than one cell at a time. If I'm right, then the harmless error will be ignored, and no error message will be displayed when you delete multiple cell values, after you change to the following code... Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Application.Intersect(Target, _ Range("$A$1:$A$10")) Is Nothing Then Application.EnableEvents = False Target.Value = UCase(Target.Value) End If Application.EnableEvents = True End Sub You will notice the only difference is the new first line.. On Error Resume Next which you could easily type into the original code. Let me know if that is the solution. Ken Johnson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Forcing All Caps in input cells
Hi Slave2Six, I forgot to add.. the error may have resulted in Events being Disabled. If this is the case then the code will stop working until Events are Enabled. The simplest way to Enable Events is to Close the workbook after saving, then reopen. Events are always enabled upon opening the workbook. Ken Johnson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Forcing All Caps in input cells
Looks like Ken has you all fixed up.
Just a note he ALL CAPS is definitely not easy to view. Quite the opposite. Gord Dibben MS Excel MVP On Fri, 28 Jul 2006 18:21:16 -0400, Slave2Six wrote: I have a data entry spreadsheet that will be used by multiple employees. For ease of viewing later, I want to ensure that all data entered into the input cells are in all caps. Is there a way to force cells to display all caps at the time of input? If not, is there a way in VBA that I can create a macro that will change the case of the contents of these cell? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Forcing All Caps in input cells
Thanks again Ken. I'll give it a shot. -- Slave2Six ------------------------------------------------------------------------ Slave2Six's Profile: http://www.excelforum.com/member.php...o&userid=36904 View this thread: http://www.excelforum.com/showthread...hreadid=566195 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Forcing All Caps in input cells
You're welcome Slave2Six. Thanks for the feedback. Ken Johnson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linking Groups of cells between workbooks | Excel Discussion (Misc queries) | |||
Data input in cells | Excel Discussion (Misc queries) | |||
Locking The Cells with Input At The Time of Saving The File | Excel Discussion (Misc queries) | |||
Convert data of cells to any type: Number, Date&Time, Text | Excel Discussion (Misc queries) | |||
How do I match two cells and input a value in a different cell? | Excel Worksheet Functions |