Copying Cell Conflict with Worksheet Change to UpperCase

[Chris]

Hello, I am using the following subroutine (Copy Last Row in J Column)
to copy the last cell in J column to the one below at the bottom of my
worksheet.

When I disable the Private Sub Worksheet_Change(ByVal Target As Range),
by placing single quotation marks against the code, then the subroutine
(Copy Last Row in J Column) works well.

When I enable the Private Sub Worksheet_Change(ByVal Target As Range),
by removing the single quotation marks against the code, then the
subroutine (Copy Last Row in J Column) does not work at all.


Could someone please help as I still want to use Upper Case upon cell
entry for my worksheet.

Any help would be greatly appreciated.

Kind regards,

Chris.

Sub Copy_Last_Row_In_J_Column()

' Copy Last Row in J Column

Application.ScreenUpdating = False

With Sheets("Register")

.Cells(.Rows.Count, "J").End(xlUp).Copy _
Destination:=.Cells(.Rows.Count, "J") _
.End(xlUp).Offset(1, 0)
End With

End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then
Exit Sub
End If
On Error GoTo ErrHandler:
If Not Application.Intersect(Me.Range("A1:U50000"), Target) Is
Nothing Then
If IsNumeric(Target.Value) = False Then
Application.EnableEvents = False
'Target.Value = StrConv(Target.Text, vbLowerCase)
Target.Value = StrConv(Target.Text, vbUpperCase)
'Target.Value = StrConv(Target.Text, vbProperCase)
Application.EnableEvents = True
End If
End If
ErrHandler:
Application.EnableEvents = True
End Sub

 

[Mike H]

Chris,

Your worksheet change event will have ensured any text value in column J is
in upper case so you can disable events when you do this copy and the 2 subs
should get on well together. having said that I'm struggling to find a
conflict with events enabled!

Sub Copy_Last_Row_In_J_Column()

' Copy Last Row in J Column

Application.ScreenUpdating = False
Application.EnableEvents = False
With Sheets("Register")
.Cells(.Rows.Count, "J").End(xlUp).Copy _
Destination:=.Cells(.Rows.Count, "J") _
.End(xlUp).Offset(1, 0)
End With
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

Mike

 

[Mike Fogleman]

You need to post your code and where your code resides ( standard code
module, Worksheet module, etc.). At first guess, in your Copy Last Row in J
Column, try disabling events:
Sub MySub()
Application.EnableEvents = False
'your copy code
Application.EnableEvents = True
End Sub

This may stop the Worksheet_Change event from firing when the other does the
copy.
Mike F

 

[Chris]

Thanks Mike for your reply - very much appreciated. Yes, I will in
future state where my code resides - sorry about that.

Kind regards,

Chris.

 

[Chris]

Hi Mike H, thanks for your reply - much appreciated. I did what Mike
Fogleman suggested and it all works fine. Thanks again for your kind
assistance.

Cheers,

Chris.