Excel VBA to automatically capitalise causing error when pasting

Question

The code below is causing an error when I paste information onto the sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim A1 As Range
    Set A1 = Range("A:I")
    If Not Intersect(Target, A1) Is Nothing Then
        Application.EnableEvents = False
            Target.Value = UCase(Target.Value)
        Application.EnableEvents = True
    End If
End Sub

The code is specific to the sheet (right click on tab an view code).

Could you please advise on how to fix this?

Thanks in advance.

Do you have any more information on the error that is being thrown? — BenCamps, Dec 17 '18 at 15:39

score 3 · Answer 1 · answered Dec 17 '18 at 15:37

3

Loop over the intersection cells if they exists:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim A1 As Range, intr As Range, r As Range
    Set A1 = Range("A:I")
    Set intr = Intersect(A1, Target)
    If Not intr Is Nothing Then
        Application.EnableEvents = False
            For Each r In intr
                r.Value = UCase(r.Value)
            Next r
        Application.EnableEvents = True
    End If
End Sub

answered Dec 17 '18 at 15:37

Gary's Student

95,722
10
59
99

1

Without error control, this will still have issue if *r* contains a worksheet error. – Dec 17 '18 at 15:41
Hi! Thanks for that. Just one issue, the date when copied is changing from 17/12/2018 to 12/17/2018. Don't think it was doing this before. Any ideas? – Jason F Dec 17 '18 at 15:48
It is questionable whether the OP needs `For Each r In intr` or `For Each r In Target`. But I can see the logic in both of them. – Vityata Dec 17 '18 at 16:04
@JasonF You could check to see if the data type of r.value is a date after you change it to upper case and then change the number format to whatever you want – Jchang43 Dec 17 '18 at 16:28

score 0 · Answer 2 · answered Dec 17 '18 at 16:02

0

The error is because of Target.Value = UCase(Target.Value) is not suitable for multiple cells. Thus a loop is needed:

Private Sub Worksheet_Change(ByVal Target As Range)

    On Error GoTo WorksheetChange_Error

    Application.EnableEvents = False
    Dim A1 As Range
    Set A1 = Range("A:I")

    If Not Intersect(Target, A1) Is Nothing Then
            Dim myCell As Range
            For Each myCell In Target.Cells
                myCell = UCase(myCell)
            Next
    End If
    Application.EnableEvents = True
    Exit Sub

WorksheetChange_Error:

    Application.EnableEvents = True
    MsgBox Err.Description

End Sub

The error handler is used to reset the EnableEvents = True, if some kind of unexpected error comes.

answered Dec 17 '18 at 16:02

Vityata

42,633
8
55
100

Hi Vityata. Thanks so much. That works great. I'm not sure this is related but there's a new issue when copying information onto this worksheet now. The date is changing fomr 17/12/2018 to 12/17/2018. Any ideas on how to fix this? – Jason F Dec 17 '18 at 16:10
Also, with this update, the undo function appears to have been disabled. Any suggestions? – Jason F Dec 17 '18 at 16:19
@JasonF - This is for the undo - https://stackoverflow.com/questions/7798575/undo-history-button-clear-after-run-macro-excel – Vityata Dec 17 '18 at 18:28
@JasonF - to fix the date, manually record a macro, in which you get the correct date. Then use the recorded code to format your VBA. – Vityata Dec 17 '18 at 18:28

Excel VBA to automatically capitalise causing error when pasting

2 Answers2