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

*** Sent via Developersdex http://www.developersdex.com ***

 

[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

 

[Simon Lloyd]

Try using
Code
-------------------
Application.EnableEvents = Fals
-------------------
at the begining of your copy code and then
Code
-------------------
Application.EnableEvents = Tru
-------------------
at the end of your copy code like your Chnageevent code

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 m
worksheet

When I disable the Private Sub Worksheet_Change(ByVal Target As Range)
by placing single quotation marks against the code, then the subroutin
(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 th
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 cel
entry for my worksheet

Any help would be greatly appreciated

Kind regards

Chris
Code
-------------------
Sub Copy_Last_Row_In_J_Column(

' Copy Last Row in J Colum

Application.ScreenUpdating = Fals

With Sheets("Register"

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

End Su


Private Sub Worksheet_Change(ByVal Target As Range
If Target.Cells.Count > 1 The
Exit Su
End I
On Error GoTo ErrHandler
If Not Application.Intersect(Me.Range("A1:U50000"), Target) I
Nothing The
If IsNumeric(Target.Value) = False The
Application.EnableEvents = Fals
'Target.Value = StrConv(Target.Text, vbLowerCase
Target.Value = StrConv(Target.Text, vbUpperCase
'Target.Value = StrConv(Target.Text, vbProperCase
Application.EnableEvents = Tru
End I
End I
ErrHandler
Application.EnableEvents = Tru
End Su
-------------------
*** Sent via Developersdex 'Developersdex.com - The Web Developer
Index and Directory' (http://www.developersdex.com) **

--
Simon Lloy

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com

 

[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 Simon - your suggestion worked well - very much appreciated,

Cheers,

Chris.



*** Sent via Developersdex http://www.developersdex.com ***

 

[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.



*** Sent via Developersdex http://www.developersdex.com ***

 

[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.

*** Sent via Developersdex http://www.developersdex.com ***