Forcing Caps and Spell Checker Settings

[Ben]

Hi,

I have two questions.

1) How do you set the cells in Excel to auto change all
letters entered to be all caps regardless of how it was
entered?

2) How do you set Spell Checker to run after every time
you exit an individual cell in Excel?

Thanks,
Ben

 

[Jim Cone]

Ben,

Question 1: Press the Caps Lock key?

Question 2: You will have to use programming code...
Right-click the worksheet tab, select view code.
Delete all text in the large window on the right.
Paste in the following:
'-----------------------------------------------
Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Me.CheckSpelling
End Sub
'------------------------------------------------

Regards,
Jim Cone
San Francisco, CA

 

[Frank Kabel]

Hi
see below

1) How do you set the cells in Excel to auto change all
letters entered to be all caps regardless of how it was
entered?

This can only (AFAIK) be achieved with VBA. E.g. put the following
code in your worksheet module. It will automatically change all entries
to upper case in column A:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A:A")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
With Target
If .Value <> "" Then
Application.EnableEvents = False
.value = UCase(.value)
End If
End With

CleanUp:
Application.EnableEvents = True
End Sub

2) How do you set Spell Checker to run after every time
you exit an individual cell in Excel?

quite similar to the above. I just added a new line for spellchecking.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A:A")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
With Target
If .Value <> "" Then
Application.EnableEvents = False
.value = UCase(.value)
End If
.CheckSpelling ' Spellcheck added
End With

CleanUp:
Application.EnableEvents = True
End Sub

 

[Guest]

Thanks.

Ben

-----Original Message-----
Ben,

Question 1: Press the Caps Lock key?

Question 2: You will have to use programming code...
Right-click the worksheet tab, select view code.
Delete all text in the large window on the right.
Paste in the following:
'-----------------------------------------------
Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Me.CheckSpelling
End Sub
'------------------------------------------------

Regards,
Jim Cone
San Francisco, CA




.

 

[Ben]

Frank,

1. The uppercase works great is there a way to have it
change it to the Uppercase format like the 'PROPER'
function in Excel.

2. I must be missing something the spelling is not
working?

-------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A")) Is Nothing Then
Exit Sub
On Error GoTo CleanUp:
With Target
If .Value <> "" Then
Application.EnableEvents = False
.Value = UCase(.Value)
End If
.CheckSpelling ' Spellcheck added
End With

CleanUp:
Application.EnableEvents = True
End Sub
---------------------------------------------

 

[Frank Kabel]

Hi
1. change UCase(.value) to application.worksheetfunction.proper(.value)

2. What do you mean with 'not working' Tested it and the spellchecking
is invoked

 

[Peter atherton]

Ben the answer to 1 is that you can't unless you use VB.
To convert lower case value you can use the UPPER
worksheet function to convert values to upper. But this
has to be done in a Helper column. and then converted back
to values with Edit, PasteSpecial, Values.

VB code will convert values using the UCase Function in
Excel. If you explain what columns are involeved someone
will give you the code.

Regards
Peter

 

[Guest]

Frank,

You are right it is working sorry!

Thanks again for the help!!

Ben