Changing case on data entry

[rob]

Hello everyone,
I am currently working on a very basic problem which includes having the
person type in there name, and it must be in uppercase. I've read the
article from cpearson.com, about changing case on data entry, and i
understand what needs to be done, but how do i incorporate that
worksheet_change string ( just on one cell, d3) into my workbook. Is there
anything that might have to be changed in the change string that I'm missing?
I've never been this deep into excel, and i don't know where to turn for
basic information on how to get this done. Any information would be greatly
appreciated

rob

 

[Frank Kabel]

Hi
have you checked Chip's article on event coding:
http://www.cpearson.com/excel/events.htm

 

[rob]

Frank,
I have checked the article, and it's great, and i have found out most of my
answers between the article and the one post with 16 reply's on uppercase. I
didn't know about right clicking on the sheet and looking for the code. The
event coding works fine, now i have got to find the article that says how i
limit that down to 1 cell and not a range. thanks for the quick reply.

rob

 

[Bob Phillips]

If Target.Address = "$A$16" Then

as an example

 

[rob]

bob,
I'm using the worksheet change from the cpearson website:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("a1:a10")) Is Nothing Then
Target(1).Value = UCase(Target(1).Value)
End If
Application.EnableEvents = True
End Sub

How do I incorporate your suggestion, say for cell D3?

rob

 

[rob]

Bob,
Thanks for your support but I played around and got it. thank you, for
other 'newbies' like me here is the single cell uppercase string, just change
the target.address for your application:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Address = "$D$3" Then
Target(1).Value = UCase(Target(1).Value)
End If
Application.EnableEvents = True
End Sub

 

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Address = "$D$3"Then
Target.Value = UCase(Target.Value)
End If
Application.EnableEvents = True
End Sub

 

[Gord Dibben]

rob

Right-click on your sheet tab and "View Code".

Paste this code in the module. Only affects D3.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Me.Range("D3")) Is Nothing Then Exit Sub
On Error GoTo Cleanup
Application.EnableEvents = False
With Target
Target.Formula = UCase(Target.Formula)
End With
Cleanup:
Application.EnableEvents = True
End Sub

Gord Dibben Excel MVP