all caps in certain cells excel 2003 Runtime error 424 object required 1

[vba317]

I am running excel 2003. I am trying to write a macro that puts a certain range of calls all caps. In the macro I am using I keep on getting an error 424 object required. Any help would be appreciated.

Tom

Sub ChgAllCaps()
'
' ChgAllCaps Macro
' Macro recorded 2/6/2013 by tdonahue
If Not (Application.Intersect(Target, Range("D8:H105")) Is Nothing) Then
    With Target
        If Not .HasFormula Then
            .Value = UCase(.Value)
        End If
    End With
End If
End Sub

 

[SkipVought]

Hi, vba317. How about forum707 for VBA?

What is Target??? It is 1) undeclared and 2) unassigned!



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[vba317]

Since I want to use my current spreadsheet do I use activesheet?

Tom

 

[vba317]

I have realized the error of my ways. I did have to declare what the target is (A Range), but I was putting the code in a macro. I needed to put the code in the worksheet change event. Which I had to right click on the name tab of the spreadsheet and click on view code. Now the code works perfectly.

Private Sub Worksheet_Change(ByVal Target As Range)
'Excel Worksheet Change event
If Not (Application.Intersect(Target, Range("D8:H105")) Is Nothing) Then
    With Target
        If Not .HasFormula Then
            .Value = UCase(.Value)
        End If
    End With
End If
End Sub

 

[xlbo]

I would add

application.enableevents = false

application.enableevents = true

around your code as if you are changing the value of a cell in your target range you will end up in an infinite loop

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question