Forcing Uppercase on Input 2

[shytott]

Having just been told there is no standard cell formatting that will enforce any input to be in uppercase, would anyone know how this could be achived in VB. This is not a sheet wide requirement, just over a block of 6 columns ie cols F - K. Also, how would said code be implemented?

Thanks

 

[AnotherHiggins]

Right click on the sheet tab and select View Code.

Paste in the following:

Private Sub Worksheet_Change(ByVal Target As Range)
Target = StrConv(Target, vbUpperCase)
End Sub

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[SkipVought]

Hi,

This can be done with the Worksheet_Change event.

Right click the sheet tab and select View code

paste this code in the code window.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range
    If Target.Count > 1 Then Exit Sub
    If Not Application.Intersect(Target, Range("F:K")) Is Nothing Then
        Application.EnableEvents = False
        With Target
            .Value = UCase(.Value)
        End With
        Application.EnableEvents = True
    End If
End Sub

Skip,

_[red][/red]

 

[shytott]

Thank you chaps - it works a treat!

 

[SkipVought]

Geez, John. Six minutes you beat me by! My DSL must be ...

Damn SLow today! ;-)

Skip,

_[red][/red]

 

[AnotherHiggins]

Those nimble fingers are getting slow in your old age!

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.