| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I set all caps as the default?
I want to set my worksheet up so that all text entered is in caps. I do not
want to have to toggle the Caps Lock key to do this. Is there a way to make this work? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I set all caps as the default?
Mark,
There are VB solutions to this but as you want all caps then pressing the capslock key once doesn't seem to onerous. Mike "Mark Barnard" wrote: I want to set my worksheet up so that all text entered is in caps. I do not want to have to toggle the Caps Lock key to do this. Is there a way to make this work? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I set all caps as the default?
Hi Mike,
Thank you for your post. I agree, however, this is not what the user wants. I am a long way from an expert in VB, so any help/advice/expertise you might have would be appreciated. Thanks! Respectfully, Mark "Mike H" wrote: Mark, There are VB solutions to this but as you want all caps then pressing the capslock key once doesn't seem to onerous. Mike "Mark Barnard" wrote: I want to set my worksheet up so that all text entered is in caps. I do not want to have to toggle the Caps Lock key to do this. Is there a way to make this work? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I set all caps as the default?
Mark,
Right click your worksheet, view code and paste this in Private Sub Worksheet_Change(ByVal Target As Range) Dim MyRange As Range On Error Resume Next Application.EnableEvents = False For Each MyRange In Selection.SpecialCells(xlCellTypeConstants, xlTextValues).Cells If Err.Number = 0 Then MyRange.Value = StrConv(MyRange.Text, vbUpperCase) End If Next MyRange Application.EnableEvents = True End Sub Mike "Mark Barnard" wrote: Hi Mike, Thank you for your post. I agree, however, this is not what the user wants. I am a long way from an expert in VB, so any help/advice/expertise you might have would be appreciated. Thanks! Respectfully, Mark "Mike H" wrote: Mark, There are VB solutions to this but as you want all caps then pressing the capslock key once doesn't seem to onerous. Mike "Mark Barnard" wrote: I want to set my worksheet up so that all text entered is in caps. I do not want to have to toggle the Caps Lock key to do this. Is there a way to make this work? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I set all caps as the default?
Hi Mike,
Thanks for this. Stupid question #3: Right click exactly where? I do not find "View Code". Sorry... Thanks... Mark "Mike H" wrote: Mark, Right click your worksheet, view code and paste this in Private Sub Worksheet_Change(ByVal Target As Range) Dim MyRange As Range On Error Resume Next Application.EnableEvents = False For Each MyRange In Selection.SpecialCells(xlCellTypeConstants, xlTextValues).Cells If Err.Number = 0 Then MyRange.Value = StrConv(MyRange.Text, vbUpperCase) End If Next MyRange Application.EnableEvents = True End Sub Mike "Mark Barnard" wrote: Hi Mike, Thank you for your post. I agree, however, this is not what the user wants. I am a long way from an expert in VB, so any help/advice/expertise you might have would be appreciated. Thanks! Respectfully, Mark "Mike H" wrote: Mark, There are VB solutions to this but as you want all caps then pressing the capslock key once doesn't seem to onerous. Mike "Mark Barnard" wrote: I want to set my worksheet up so that all text entered is in caps. I do not want to have to toggle the Caps Lock key to do this. Is there a way to make this work? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I set all caps as the default?
Right-click on the sheet tab and "View Code"
Paste the code into that sheet module. Alt + q to return to the sheet window. Gord Dibben MS Excel MVP On Mon, 10 Dec 2007 07:14:06 -0800, Mark Barnard wrote: Hi Mike, Thanks for this. Stupid question #3: Right click exactly where? I do not find "View Code". Sorry... Thanks... Mark "Mike H" wrote: Mark, Right click your worksheet, view code and paste this in Private Sub Worksheet_Change(ByVal Target As Range) Dim MyRange As Range On Error Resume Next Application.EnableEvents = False For Each MyRange In Selection.SpecialCells(xlCellTypeConstants, xlTextValues).Cells If Err.Number = 0 Then MyRange.Value = StrConv(MyRange.Text, vbUpperCase) End If Next MyRange Application.EnableEvents = True End Sub Mike "Mark Barnard" wrote: Hi Mike, Thank you for your post. I agree, however, this is not what the user wants. I am a long way from an expert in VB, so any help/advice/expertise you might have would be appreciated. Thanks! Respectfully, Mark "Mike H" wrote: Mark, There are VB solutions to this but as you want all caps then pressing the capslock key once doesn't seem to onerous. Mike "Mark Barnard" wrote: I want to set my worksheet up so that all text entered is in caps. I do not want to have to toggle the Caps Lock key to do this. Is there a way to make this work? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I set all caps as the default?
Hi Mr. Dibben,
Thank you...I was able to do this exactly as you said. However, when I type text into a cell, it is not in caps. Usually, when I get mad at my computer, it is because it is doing EXACTLY what I tell it to do! Heehee Any suggestions? Thank you! Sincerely, Mark "Gord Dibben" wrote: Right-click on the sheet tab and "View Code" Paste the code into that sheet module. Alt + q to return to the sheet window. Gord Dibben MS Excel MVP On Mon, 10 Dec 2007 07:14:06 -0800, Mark Barnard wrote: Hi Mike, Thanks for this. Stupid question #3: Right click exactly where? I do not find "View Code". Sorry... Thanks... Mark "Mike H" wrote: Mark, Right click your worksheet, view code and paste this in Private Sub Worksheet_Change(ByVal Target As Range) Dim MyRange As Range On Error Resume Next Application.EnableEvents = False For Each MyRange In Selection.SpecialCells(xlCellTypeConstants, xlTextValues).Cells If Err.Number = 0 Then MyRange.Value = StrConv(MyRange.Text, vbUpperCase) End If Next MyRange Application.EnableEvents = True End Sub Mike "Mark Barnard" wrote: Hi Mike, Thank you for your post. I agree, however, this is not what the user wants. I am a long way from an expert in VB, so any help/advice/expertise you might have would be appreciated. Thanks! Respectfully, Mark "Mike H" wrote: Mark, There are VB solutions to this but as you want all caps then pressing the capslock key once doesn't seem to onerous. Mike "Mark Barnard" wrote: I want to set my worksheet up so that all text entered is in caps. I do not want to have to toggle the Caps Lock key to do this. Is there a way to make this work? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I set all caps as the default?
Mark
Mike's code works for me when pasted into the sheet module as instructed. Re-posted without the line-wrap. Private Sub Worksheet_Change(ByVal Target As Range) Dim MyRange As Range On Error Resume Next Application.EnableEvents = False For Each MyRange In Selection.SpecialCells(xlCellTypeConstants, _ xlTextValues).Cells If Err.Number = 0 Then MyRange.Value = StrConv(MyRange.Text, vbUpperCase) End If Next MyRange Application.EnableEvents = True End Sub Gord On Mon, 10 Dec 2007 17:56:00 -0800, Mark Barnard wrote: Hi Mr. Dibben, Thank you...I was able to do this exactly as you said. However, when I type text into a cell, it is not in caps. Usually, when I get mad at my computer, it is because it is doing EXACTLY what I tell it to do! Heehee Any suggestions? Thank you! Sincerely, Mark "Gord Dibben" wrote: Right-click on the sheet tab and "View Code" Paste the code into that sheet module. Alt + q to return to the sheet window. Gord Dibben MS Excel MVP On Mon, 10 Dec 2007 07:14:06 -0800, Mark Barnard wrote: Hi Mike, Thanks for this. Stupid question #3: Right click exactly where? I do not find "View Code". Sorry... Thanks... Mark "Mike H" wrote: Mark, Right click your worksheet, view code and paste this in Private Sub Worksheet_Change(ByVal Target As Range) Dim MyRange As Range On Error Resume Next Application.EnableEvents = False For Each MyRange In Selection.SpecialCells(xlCellTypeConstants, xlTextValues).Cells If Err.Number = 0 Then MyRange.Value = StrConv(MyRange.Text, vbUpperCase) End If Next MyRange Application.EnableEvents = True End Sub Mike "Mark Barnard" wrote: Hi Mike, Thank you for your post. I agree, however, this is not what the user wants. I am a long way from an expert in VB, so any help/advice/expertise you might have would be appreciated. Thanks! Respectfully, Mark "Mike H" wrote: Mark, There are VB solutions to this but as you want all caps then pressing the capslock key once doesn't seem to onerous. Mike "Mark Barnard" wrote: I want to set my worksheet up so that all text entered is in caps. I do not want to have to toggle the Caps Lock key to do this. Is there a way to make this work? |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I set all caps as the default?
Dear Mr. Dibben,
It worked perfectly...thank u! Now, if I want to get really cute, can I do the same thing, only for two particular cells? A little history may help...I am writing this for an aviation application. Each airport in the world is identified by a unique 4-letter identifier. I have a cell for the departure airport and the destination airport. The user wants these two cells in all upper case. The remaining cells he wants to remain in title case. Sorry to be a pain...if it's a hassle, don't worry about it. You and Mike have been great help already. Sincerely, Mark "Gord Dibben" wrote: Mark Mike's code works for me when pasted into the sheet module as instructed. Re-posted without the line-wrap. Private Sub Worksheet_Change(ByVal Target As Range) Dim MyRange As Range On Error Resume Next Application.EnableEvents = False For Each MyRange In Selection.SpecialCells(xlCellTypeConstants, _ xlTextValues).Cells If Err.Number = 0 Then MyRange.Value = StrConv(MyRange.Text, vbUpperCase) End If Next MyRange Application.EnableEvents = True End Sub Gord On Mon, 10 Dec 2007 17:56:00 -0800, Mark Barnard wrote: Hi Mr. Dibben, Thank you...I was able to do this exactly as you said. However, when I type text into a cell, it is not in caps. Usually, when I get mad at my computer, it is because it is doing EXACTLY what I tell it to do! Heehee Any suggestions? Thank you! Sincerely, Mark "Gord Dibben" wrote: Right-click on the sheet tab and "View Code" Paste the code into that sheet module. Alt + q to return to the sheet window. Gord Dibben MS Excel MVP On Mon, 10 Dec 2007 07:14:06 -0800, Mark Barnard wrote: Hi Mike, Thanks for this. Stupid question #3: Right click exactly where? I do not find "View Code". Sorry... Thanks... Mark "Mike H" wrote: Mark, Right click your worksheet, view code and paste this in Private Sub Worksheet_Change(ByVal Target As Range) Dim MyRange As Range On Error Resume Next Application.EnableEvents = False For Each MyRange In Selection.SpecialCells(xlCellTypeConstants, xlTextValues).Cells If Err.Number = 0 Then MyRange.Value = StrConv(MyRange.Text, vbUpperCase) End If Next MyRange Application.EnableEvents = True End Sub Mike "Mark Barnard" wrote: Hi Mike, Thank you for your post. I agree, however, this is not what the user wants. I am a long way from an expert in VB, so any help/advice/expertise you might have would be appreciated. Thanks! Respectfully, Mark "Mike H" wrote: Mark, There are VB solutions to this but as you want all caps then pressing the capslock key once doesn't seem to onerous. Mike "Mark Barnard" wrote: I want to set my worksheet up so that all text entered is in caps. I do not want to have to toggle the Caps Lock key to do this. Is there a way to make this work? |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I set all caps as the default?
On Dec 12, 4:39 am, Gord Dibben <gorddibbATshawDOTca wrote:
.... Re-posted without the line-wrap. Here is a small tool I developed, to help check for line-wrap. <http://www.physci.org/twc.jnlp HTH -- Andrew T. PhySci.org |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I set all caps as the default?
Some modifications to original code.
Private Sub Worksheet_Change(ByVal Target As Excel.Range) Const WS_RANGE As String = "A1, A5" 'add more cells if needed 'like "A1, A5, A10, B1, B5" On Error GoTo ErrHandler Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then For Each cell In Target Target.Formula = UCase(Target.Formula) Next cell End If ErrHandler: Application.EnableEvents = True End Sub Gord On Tue, 11 Dec 2007 18:12:06 -0800, Mark Barnard wrote: Dear Mr. Dibben, It worked perfectly...thank u! Now, if I want to get really cute, can I do the same thing, only for two particular cells? A little history may help...I am writing this for an aviation application. Each airport in the world is identified by a unique 4-letter identifier. I have a cell for the departure airport and the destination airport. The user wants these two cells in all upper case. The remaining cells he wants to remain in title case. Sorry to be a pain...if it's a hassle, don't worry about it. You and Mike have been great help already. Sincerely, Mark |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I set caps lock as default? | Excel Worksheet Functions | |||
| How do I set default for a column so that first letters are caps? | New Users to Excel | |||
| How to change ALL CAPS to Initial Caps only? | Excel Worksheet Functions | |||
| How can I convert all Caps to first letter caps in Excel? | Excel Worksheet Functions | |||
| excel sheet all caps and needs to be only the first letter caps.. | Excel Discussion (Misc queries) | |||
Linear Mode
