VBA code to change text entered in cell to uppercase

[KMH]

Hi,

I have several cells in a worksheet that I would like to have automatically change to uppercase once information is entered in the cells.

I've tried the below, but it isn't working for me:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("B4,G3,F7:H15,F23,C27,E27,F46:G50,G74:I79,C91,E95")) Is Nothing Then Exit Sub
Application.EnableEvents = False
Target.Value = UCase(Target.Value)
Application.EnableEvents = True

End Sub

I'd be grateful for any other suggestions as to code I could try.

Thanks,

 

[rlv01]

Using your code, if I enter text in cell B3, it is changed to upper case. If I enter text in cell B4, the case is not changed.

What do you mean by "isn't working"?

 

[KMH]

Using your code, if I enter text in cell B3, it is changed to upper case. If I enter text in cell B4, the case is not changed.

What do you mean by "isn't working"?

It doesn't change text in the defined range to uppercase

 

[KMH]

The one below works, but I have to manually run it. It doesn't do it automatically as I come out of the cell. Also it's running the loop for a while.

Ideally, I'd like for the text in the cell to change as soon as I exit the cell and without having to create a macro button for it.

Sub Uppercase()
' Loop to cycle through each cell in the specified range.
For Each x In Range("B4, G3, F7:H15, F23, C27, E27, F46:G50, G74:I79, C91, E95")
' Change the text in the range to uppercase letters.
x.Value = UCase(x.Value)
Next
End Sub

 

[KMH]

TEST 1 CSPR Sub-Committee Points Schedule Calculator UNLOCKED v2.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Permanent Residence Points Assessment
2
3	File Number:	000000				Agent:
4	Name:	LAST NAME, FIRST NAME
5
6	Total Points:	0.00				Dependants:		D.O.B.
7
8	Factor 1:	0.00
9	Factor 2:	0.00
10	Factor 3:	0.00
11	Factor 4:	0.00
12	Factor 5:	0.00
13	Factor 6:	0.00
14	Factor 7:	0.00
15	Factor 8:	0.00
16	Factor 9:	0.00
17	Deductibles:	0.00
18
19
20
21	Factor 1 - Occupation								0	/15
22
23		Currently employed?	N		Occupation:
24
25	Factor 2 - Education, Training & Experience								0.00	/25
26			Start Date		Date of Application
27	2a.	Years of experience						0.00
28
29	2b.	Education:	None of the above					0
30
31	Factor 3 - Local Investments								0.00	/30
32		1. Investment in Property						0.00
33		2. Investment in a locally licensed company						0.00
34		3. Combined (in the event that neither 1 or 2 meets the minimum criteria individually)						0.00
35
36	Applicant:	12 month Income				No. of persons that jointly own the property(ies) (including the applicant)	0
37		5 yr Income
38		Annual Income
39
40	Spouse:	12 month Income				Property jointly owned with spouse:	N
41		5 yr Income
42		Annual Income
43
44		90% of 12 mth income	$0.00			Property Details
45		40% of 5 yr Income	$0.00			Block	Parcel
46
47		Children:	N
48	# on non-accompanying dependant children		0	0
49	# of accompanying dependant children (school age)		0	0
50	# of accompanying dependant children (non-school age)		0	0
51
52		OPTION 1			OPTION 2
53		Property - purchase price			Property - down payment
54		Stamp Duty			Stamp Duty
55		Mortgage amount owing			Payments to date
56		Total	$0.00		Total			$0.00
57
58	Investment in Local Company		$0.00
59
60	Factor 4 - Financial Stability								0.00	/30
61		Savings	$0.00		0.00	0		0
62		Salary & Income	$0.00					0
63
64
65
66
67	Factor 5 - Community Involvement								0.00	/20
68			# of Years	# of Hours Per Year
69	1a. Training &/or Mentoring of Caymanians outside of normal work hours or related employer sponsored activities					0
70	1b. Personal sponsorship of a Caymanian's tertiary training >$3,500 p/a					0
71	1c. Actively assist in the Rehabilitation & Mentoring of Offenders					0		0
72
73	2a. Participation & Assistance in a Youth Programme					0	Name(s) of Persons Entities:			Section
74	2b. Training & Mentoring of Caymanians within normal work related / sponsored activities					0
75	2c. Participation and assistance in a sports programme					0
76	2d. Participation and assistance in an arts programme					0
77	2e. Participation and assistance in a local service club activities					0
78	2f. Participation and assistance in a local church programme activities					0
79	2g. Personal donations to community minded activities of a minimum of $2,000 p/a					0
80	2h. Volunteering for non-profit, charitable or voluntary organisations					0		0
81
82	Factor 6 - History and Culture Test								0.0	/20
83		Test Results		/40
84
85	Factor 7 - Possessing Close Caymanian Connections								0	/100
86
87	Parent or Child of a Caymanian		N	0
88	Sibling or Grandparent of a Caymanian		N	0
89
90	Factor 8 - Demographic and Cultural Diversity								0	/10
91		Nationality
92
93	Factor 9 - Age Distribution								0	/10
94					Date of Birth
95		Age at Date of Application	0
96
97	Deductible Components								0
98				Explanation				# of Points Deducted
99		Criminal Convictions	N
100		Health Issues	N
101	Administrative fines levied in relation to statutory offenses		N
102		Lack of reasonably funded pension plan	N
103		Other mitigating Factors	N
104
105
106
107	Comments:
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
ASSESSMENT

Cell Formulas
Range	Formula
B6	B6	=SUM(B8:B16)-B17
B8	B8	=I21
B9	B9	=I25
B10	B10	=I31
B11	B11	=I60
B12	B12	=I67
B13	B13	=I82
B14	B14	=I85
B15	B15	=I90
B16	B16	=I93
B17	B17	=I97
I21	I21	=IF(OR(C23="",C23="N"),0,15)
I25	I25	=H27+H29
H27	H27	=IF((Calculations!E16+(Calculations!E17/12))<10,Calculations!E16+(Calculations!E17/12),10)
H29	H29	=VLOOKUP(C29,Calculations!A2:B9,2, FALSE)
I31	I31	=IF(($H$32+$H$33+$H$34>30),30,IF($H$32+$H$33+$H$34<30,$H$32+$H$33+$H$34,0))
H32	H32	=IF(VLOOKUP(MAX(Calculations!$I$2:$I$5), Calculations!$I$2:$I$5,1,0)>30,30,VLOOKUP(MAX(Calculations!$I$2:$I$5), Calculations!$I$2:$I$5,1,0))
H33	H33	=IF(VLOOKUP(MAX(Calculations!$I$8:$I$9), Calculations!$I$8:$I$9,1,0)>30,30,VLOOKUP(MAX(Calculations!$I$8:$I$9), Calculations!$I$8:$I$9,1,0))
H34	H34	=IF(VLOOKUP(MAX(Calculations!$I$12:$I$15), Calculations!$I$12:$I$15,1,0)>30,30,(VLOOKUP(MAX(Calculations!$I$12:$I$15), Calculations!$I$12:$I$15,1,0)))
C44	C44	=IF($G$40="Y",(((($C$36+$C$40)*0.9)*5)*0.4),((($C$36*0.9)*5)*0.4))
C45	C45	=IF($G$40="Y",(($C$37+$C$41)*0.4),($C$37*0.4))
D48	D48	=IF($C$48>0,2500*$C$48,0)
D49	D49	=IF($C$49>0,15000*$C$49,0)
D50	D50	=IF($C$50>0,12000*$C$50,0)
C56	C56	=IF(AND($G$36>2,$G$40="Y"),(($C$53+$C$54-$C$55)/$G$36)*2,IF(AND($G$36>1,$G$40="N"),(($C$53+$C$54-$C$55)/$G$36),$C$53+$C$54-$C$55))
H56	H56	=IF(AND($G$36>2,$G$40="Y"),(($H$53+$H$54+$H$55)/$G$36)*2,IF(AND($G$36>1,$G$40="N"),(($H$53+$H$54+$H$55)/$G$36),$H$53+$H$54+$H$55))
I60	I60	=IF((H61+H62)>30,30,H61+H62)
E61	E61	=IF(C61>0,(C61/C36)*100,0)
F61	F61	=IF(E61<1,0,IF(E61<1.99,3,IF(E61<2.99,6,IF(E61<3.99,9,IF(E61<4.99,12,IF(E61>=5,15,0))))))
C62	C62	=IF(AND($C$47="Y",$D$48+$D$49+$D$50>0),($C$38+$C$42)-($D$48+$D$49+$D$50),IF(AND($C$47="N",$D$48+$D$49+$D$50>0),$C$38-($D$48+$D$49+$D$50),$C$38))
H61	H61	=Calculations!$A$17
H62	H62	=IF($C$62<15000,0,IF($C$62<29999,1,IF($C$62<39999,3,IF($C$62<49999,5,IF($C$62<59999,7,IF($C$62<69999,9,IF($C$62<89999,11,IF($C$62<109999,12,IF($C$62<129000,13,IF($C$62<149999,14,IF($C$62>150000,15)))))))))))
I67	I67	=IF((H71+H80)>20,20,H71+H80)
F69:F71	F69	=IF(AND(C69>0,D69>=35),C69*2,IF(AND(C69>8,D69>=35),16,0))
H71	H71	=SUM(F69:F71)
F73:F80	F73	=IF(AND(C73>0,D73>=35),C73*1.5,IF(AND(C73>8,D73>=35),16,0))
H80	H80	=SUM(F73:F80)
I82	I82	=C83/2
I85	I85	=D87+D88
D87	D87	=IF(C87="Y",40,0)
D88	D88	=IF(C88="Y",20,0)
I90	I90	=Calculations!A29
I93	I93	=Calculations!D23
C95	C95	=IF(OR(ISBLANK(E95),ISBLANK(E27)),0,DATEDIF(E95,E27,"y"))
I97	I97	=SUM($H$99:$H$103)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B6	Expression	=$B$6<110	text	NO
B6	Expression	=$B$6>=110	text	NO

Cells with Data Validation
Cell	Allow	Criteria
C47	List	=Factor3
C87:C88	List	=Factor3
C99:C103	List	=Factor3
G40:G41	List	=Factor3
C27	Date	between 1/1/1900 and TODAY()
E27	Date	between 1/1/1900 and TODAY()
C69:C71	Any value
C73:C80	Any value
C23	List	=Factor1
J74:J79	List	=Factor5
E95	Any value
H7:H15	Any value
F36:F38	Any value
G36:G38	Whole number	>=0
C91	Any value
C29:F29	List	=Calculations!$A$2:$A$9

 

[rlv01]

It doesn't change text in the defined range to uppercase

That's because the code is written to exclude the defined range. That is, it will change B3, but not B4. If you mean for the range to be inclusive, then change to something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim MyRange As Range, R As Range

    Set MyRange = Intersect(Target, Me.Range("B4,G3,F7:H15,F23,C27,E27,F46:G50,G74:I79,C91,E95"))
    If Not MyRange Is Nothing Then
        Application.EnableEvents = False
        For Each R In MyRange
            R.Value = UCase(R.Value)
        Next R
        Application.EnableEvents = True
    End If
End Sub

 

[KMH]

That's because the code is written to exclude the defined range. That is, it will change B3, but not B4. If you mean for the range to be inclusive, then change to something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim MyRange As Range, R As Range

    Set MyRange = Intersect(Target, Me.Range("B4,G3,F7:H15,F23,C27,E27,F46:G50,G74:I79,C91,E95"))
    If Not MyRange Is Nothing Then
        Application.EnableEvents = False
        For Each R In MyRange
            R.Value = UCase(R.Value)
        Next R
        Application.EnableEvents = True
    End If
End Sub

Thank you sooooooo much!!!!!!!!!!!!!!!!! That worked perfectly!!!!!!!!!!!