Auto Capital - (Further Question)

[NoodNutt]

Mike / Gord

Just had another thought regarding capitalising.

And yep! It would be just as easy to use the <Caps Lock>, but most of the
"Gate-house" staff are all-but illiterate (as they are mostly imports and
have limited english, let alone computer skills), and they (the Company) are
losing information integrity, so here I am, trying to maintain that
continuity.

That Said! 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.

 

[Kevin Smith]

Hello,
you could also use

Private Sub Workbook_Open()

Dim C as range

Application.ScreenUpdating = False
Range("A1:K10000").select
For Each C In Selection
C.Value = Ucase(C.Value)
Next
Application.ScreenUpdating = True
End Sub

 

[Gord Dibben]

I don't know how to set a blank range to UPPER case using workbook_open
code.

Why don't you simply edit the range in the worksheet event code you were
given?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const WS_RANGE As String = "A1:K10000"
On Error GoTo ErrHandler
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Target.Formula = UCase(Target.Formula)
End If
ErrHandler:
Application.EnableEvents = True
End Sub


Gord

 

[Gord Dibben]

Kevin

That would take quite a while to run every time the workbook was opened.

And it would not auto-change case in newly entered cells.


Gord Dibben MS Excel MVP

 

[NoodNutt]

Kev

Thank you for your suggestion, although, I went with Gord's answer, I
appreciate your efforts though.

Gord

The original Code you provided had a small glitch in that it did not auto
cap until I clicked back on the cell again, dunno why it was doing it, but I
must thank you as this is by far a better alternative and works a treat.

Thx heaps to both.

Regards
Mark.

 

[Gord Dibben]

I don't know why that would occur unless you altered the posted event type
to a selectionchange event.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

But, good to hear you're happy.


Gord