Excel VBA code returns error

Question

have an excel file with this vba:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not (Application.Intersect(Target, Range("A1:A1000")) _
      Is Nothing) Then
        With Target
            If Not .HasFormula Then
                Application.EnableEvents = False
                .Value = UCase(.Value)
                Application.EnableEvents = True
            End If
        End With
    End If
End Sub

What this does, when something is inserted on those cells, it converts it to caps. Everything works just fine, just one small problem... the file is used every day by several people, so the inserted data is deleted every day, several times. what happens is if I delete one cell at a time it runs smoothly, if I delete several cells at the same time I get a run time error '13'.

how can i correct this?

score 5 · Accepted Answer · 2018-04-05 13:39:47Z

5

Loop through each matching cell in Target when Target is more than a single cell.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1:A1000") Is Nothing Then
        dim trgt as range
        for each trgt in Intersect(Target, Range("A1:A1000")
            With trgt 
                If Not .HasFormula Then
                    Application.EnableEvents = False
                    .Value = UCase(.Value)
                    Application.EnableEvents = True
                End If
            End With
         next trgt
    End If
End Sub

edited Apr 5, 2018 at 13:39

answered Apr 5, 2018 at 13:33

user4039065

Sign up to request clarification or add additional context in comments.

3 Comments

QHarr Over a year ago

If Not Intersect(Target, Range("A1:A1000")) Is Nothing Then and For Each trgt In Intersect(Target, Range("A1:A1000")) Nice btw

user4039065 Over a year ago

Thank you @QHarr!

shrivallabha.redij Over a year ago

It will be better if Application.EnableEvents = False is moved outside just below `If Not Intersect...' would be better. No need to loop that setting IMO.

QHarr · Accepted Answer · 2018-04-05 13:45:09Z

2

You can simply do the following (it is cosmetic... @Jeeped's answer is the way to go, consider the example of people pasting into more than one cell at time, but there is no taking account of that now in mine as he has done it!)

Option Explicit

 Private Sub Worksheet_Change(ByVal Target As Range)
    If Not (Application.Intersect(Target, Range("A1:A1000")) _
      Is Nothing) Then
        With Target
            If Not .HasFormula Then
                Application.EnableEvents = False
                On Error Resume Next
                .Value = UCase(.Value)
                On Error GoTo 0

                Application.EnableEvents = True
            End If
        End With
    End If
End Sub

edited Apr 5, 2018 at 13:45

answered Apr 5, 2018 at 13:32

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

2 Comments

QHarr Over a year ago

to be honest I think @Jeeped's answer is better. I am guessing his can cope with people pasting data into multiple cells in one go

questionador Over a year ago

It does, but the insertion of data is always cell to cell, so it wont add anything to my needs

Collectives™ on Stack Overflow

Excel VBA code returns error

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related