Excel 2002/2003 macros in Excel 2007/2010

[GenAMES]

Because I'm lazy & want consistency in data input, I wrote the following macro to convert text to upper case in three cells on a worksheet:

Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Value <> "" And Target = [b5] Then
Target.Value = UCase(Target.Value)
End If
If Target.Value <> "" And Target = [b6] Then
Target.Value = UCase(Target.Value)
End If
If Target.Value <> "" And Target = [b7] Then
Target.Value = UCase(Target.Value)
End If
End Sub

it might be a bit clumsy but it worked without any errors.

However, I've been migrated to Excel2010 and now the macro appears to get into a loop, asks me to "End" or "Debug" the macro & my only recourse is to save the spreadsheet, shut excel down through Task Manager and re-open Excel.

Does anyone have any clues/suggestions, please?

 

[Sektor]

What line does debugger highlight? To see this, press Debug.

 

[Marcelo Branco]

Try

Sub Worksheet_Change(ByVal Target As Excel.Range)
    [COLOR=blue]Application.EnableEvents = False
[/COLOR]    If Target.Value <> "" And Target = [b5] Then
        Target.Value = UCase(Target.Value)
    End If
    If Target.Value <> "" And Target = [b6] Then
        Target.Value = UCase(Target.Value)
    End If
    If Target.Value <> "" And Target = [b7] Then
        Target.Value = UCase(Target.Value)
    End If
    [COLOR=blue]Application.EnableEvents = True
[/COLOR]End Sub

HTH

M.

 

[GenAMES]

absolutely marvellous. worked a treat. many thanks

 

[GenAMES]

each of the if target.value<>""

 

[Marcelo Branco]

oops,,

I think you need to use UCase in the IFs like this

Sub Worksheet_Change(ByVal Target As Excel.Range)
    [COLOR=blue]Application.EnableEvents = False
[/COLOR]    If Target.Value <> "" And UCase(Target) = UCase([b5]) Then
        Target.Value = UCase(Target.Value)
    End If
    If Target.Value <> "" And UCase(Target) = UCase([b6]) Then
        Target.Value = UCase(Target.Value)
    End If
    If Target.Value <> "" And UCase(Target) = UCase([b7]) Then
        Target.Value = UCase(Target.Value)
    End If
    [COLOR=blue]Application.EnableEvents = True
[/COLOR]End Sub

M.

 

[Marcelo Branco]

absolutely marvellous. worked a treat. many thanks

You are welcome and tks for the feedback

M.
ps: see my last post. I'm not 100% sure about what you want, so, maybe, you have to use UCase, depending on what you need.