Macro Not Working? Change case when saving

[jeffcoleky]

The following macro isn't working. I have it placed in the code for Sheet1. Can anyone help me understand why it won't work? it is supposed to change data certain columns to "proper" and others to upper-case.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim cell    As Range
    Application.EnableEvents = False
    Select Case Sh.CodeName
        Case "Sheet1"
            For Each cell In Target.Cells
                Select Case cell.Column
                    Case 2, 10, 11, 15, 21, 24, 29
                        cell.Value = StrConv(cell.Text, vbUpperCase)
                    Case 5, 6, 7, 9, 12, 13, 14, 18, 19, 20, 22, 23, 27
                        cell.Value = StrConv(cell.Text, vbProperCase)
                End Select
            Next cell


    End Select
 End Sub

I'd love for it to ONLY apply to UNHIDDEN rows. However, at this point having it work on ALL cells would be better than it not working at all which is where it is now...

 

[Norie]

Right at the start you disable events but you never enable them at the end.

That means that this code will only ever run once, after that it won't be triggered as events are disabled.

Add this at the end.

Application.EnableEvents = True

 

[jeffcoleky]

Norie,

I just tried enabling events at the end and it doesn't seem to have made a difference. Here is some sample data to use:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	AAAAA	BBBBB	CCCCC	DDDDD	EEEEE	FFFFF	GGGGG	HHHHH	IIIII	JJJJJ	KKKKK	LLLLL	MMMMM	NNNNN	OOOOO	PPPPP	QQQQQ	RRRRR	SSSSSS	TTTTTTT	UUUU	VVVVV	WWWWW	XXXXXX	YYYYYY	ZZZZZ
2	aaaaa	bbbbb	ccccc	ddddd	eeeee	fffff	ggggg	hhhhh	iiiii	jjjjj	kkkkk	lllll	mmmmm	nnnnn	ooooo	ppppp	qqqqq	rrrrr	ssssss	ttttttt	uuuu	vvvvv	wwwww	xxxxxx	yyyyyy	zzzzz
3	Aaaaa	Bbbbb	Ccccc	Ddddd	Eeeee	Fffff	Ggggg	Hhhhh	Iiiii	Jjjjj	Kkkkk	Lllll	Mmmmm	Nnnnn	Ooooo	Ppppp	Qqqqq	Rrrrr	Ssssss	Ttttttt	Uuuu	Vvvvv	Wwwww	Xxxxxx	Yyyyyy	Zzzzz
Sheet1

Here is the code with your suggested change included:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim cell    As Range
    Application.EnableEvents = False
    Select Case Sh.CodeName
        Case "Sheet1"
            For Each cell In Target.Cells
                Select Case cell.Column
                    Case 2, 10, 11, 15, 21, 24, 29
                        cell.Value = StrConv(cell.Text, vbUpperCase)
                    Case 5, 6, 7, 9, 12, 13, 14, 18, 19, 20, 22, 23, 27
                        cell.Value = StrConv(cell.Text, vbProperCase)
                End Select
            Next cell
    End Select


    Application.EnableEvents = True


End Sub

 

[jeffcoleky]

Here's an example file with the macro included, not working yet: Wikisend: free file sharing service

 

[Norie]

When do you want this code to run?

 

[jeffcoleky]

When do you want this code to run?

As i input data or when i save it... whatever is faster... it doesn't matter really.

 

[Norie]

Where did you put this code?

Are you sure it's actually being triggered?

Is it only meant to run when Sheet1 is changed?

 

[jeffcoleky]

Where did you put this code?

Right clicked on the tab, edit code. Pasted in there.

Are you sure it's actually being triggered?

No, how do I confirm?

Is it only meant to run when Sheet1 is changed?

Yes.

 

[Norie]

The code you posted is for the workbook SheetChange event, and should be in the ThisWorkbook module.

However if you only want the code to run on a specific sheet, eg Sheet1, you should use that sheet's Change event which would go in the worksheet's module.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range

    Application.EnableEvents = False

    For Each cell In Target.Cells
        Select Case cell.Column
            Case 2, 10, 11, 15, 21, 24, 29
                cell.Value = StrConv(cell.Text, vbUpperCase)
            Case 5, 6, 7, 9, 12, 13, 14, 18, 19, 20, 22, 23, 27
                cell.Value = StrConv(cell.Text, vbProperCase)
        End Select
    Next cell
    
    Application.EnableEvents = True

End Sub

 

[jeffcoleky]

Norie, That worked! thanks so much for the help!