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.

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

"NoodNutt" wrote:

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.

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

On Wed, 23 Sep 2009 02:57:28 +1000, "NoodNutt" wrote:

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.

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.

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.