All Caps when data entered?

[Wolverine]

Hi,

Thanks in advance to anyone who can help me. Is there a way to make a
colums where the cells are all ALL CAPS... even if the data is put in
in lower case... I have a sheet I am working on where I input codes in
the format:

DFG-103-A

I want to make it where if someone inputs:

dfg-103-a

it automatically changes it to:

DFG-103-A

Is this possible?

 

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If .Column = 1 Then
.Value = UCase(.Value)
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[John]

Hi place the follwing within the Sheets code and change the range to suit

Rgds



Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count = 1 Then
If Not Intersect(.Cells, Range("A9:C45")) Is Nothing Then
Application.EnableEvents = False
.Value = UCase(.Value)
Application.EnableEvents = True
End If
End If
End With
End Sub

 

[Peo Sjoblom]

It is possible but can be turned off if macros are not enabled.
Right click the sheet tab where you want this to happened and
select view code, paste in the following

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("B1:B500")) Is Nothing Then
Application.EnableEvents = False
With Target
.Value = UCase(.Value)
End With
Application.EnableEvents = True
End If
End Sub

where B1:B500 is the range where this will happen, change to fit your needs

You can also use data validation, select the range (assume same range as
macro)
with B1 as active cell do data>validation>custom and use

=EXACT(B1,UPPER(B1))

click the error alert and select a message like "You need to use capital
letters!"

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

 

[Leo Heuser]

From the sheet rightclick the sheet tab and choose
"View code" or similar. Copy and insert the code below in
the righthand window:

Private Sub Worksheet_Change(ByVal Target As Range)
'Leo Heuser, 5 June, 2004
Dim Cell As Range
Dim CheckRange As Range

On Error GoTo Finito

Set CheckRange = Columns("A")

If Union(CheckRange, Target).Address = CheckRange.Address Then
Application.EnableEvents = False

For Each Cell In Target.Cells
Cell.Value = UCase(Cell.Value)
Next Cell
End If

Finito:
Application.EnableEvents = True
On Error GoTo 0
End Sub