Auto Capitalising an active cell

[NoodNutt]

G'day Everyone

can someone help fill the blanks (using 2000 format) in the following
please.

I need for each cell containing data in column of "F" to AutoCapitalise
after the user moves to any other cell.

I know in Access you can force the field to do this, but I am unsure of the
Excel equivalent.

Now, I know this next bit is way wrong, so if anyone has something to fill
the gaps in, that'd be sweet.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

activecell.Format = AutoCapital

End Sub

TIA
Regards
Mark.

 

[Mike H]

Hi,

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub

If Not Intersect(Target, Range("f:f")) Is Nothing Then
If Not Target.HasFormula Then
On Error Resume Next
Application.EnableEvents = False

Target.Value = UCase(Target.Formula)

Application.EnableEvents = True
On Error GoTo 0
End If
End If

End Sub

Mike

 

[Gord Dibben]

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column <> 6 Then Exit Sub
On Error GoTo ErrHandler
Application.EnableEvents = False
Target.Formula = UCase(Target.Formula)
ErrHandler:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP

 

[NoodNutt]

Thx heaps to both Mike & Gord.

One day when I truly get organised, I will have to store all these helpful
hints.

Cheers & Thx again.

Regards
Mark.

 

[NoodNutt]

Mike / Gord

Just had another thought regarding capitalising.

Can I set the entire range as <Caps> using the Workbook On_Open event.

something like:

Private Sub Workbook_Open()

Dim MyWB As Workbook
Dim MyWS As Worksheet
Dim MySR As Range

Application.ScreenUpdating = False

Set MyWB = ActiveWorkbook
Set MyWS = GateLog

MySR = "A1:K10000"
MyWS = UCase(MySR)

Application.ScreenUpdating = True

End Sub



This resulted in the following error:

Runtime Error '424'
Object Required

Appreciate any guidance.

TIA
Mark.