Copying Cell Conflict with Worksheet Change to UpperCase
 

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 ***

Copying Cell Conflict with Worksheet Change to UpperCase
 

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

"Chris" wrote:

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 ***

Copying Cell Conflict with Worksheet Change to UpperCase
 

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

Chris;248654 Wrote:
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.


Code:
--------------------

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 'Developersdex.com - The Web Developers
Index and Directory' (http://www.developersdex.com) ***


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=69397

Copying Cell Conflict with Worksheet Change to UpperCase
 

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" wrote in message
...
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 ***

Copying Cell Conflict with Worksheet Change to UpperCase
 

Thanks Simon - your suggestion worked well - very much appreciated,

Cheers,

Chris.



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

Copying Cell Conflict with Worksheet Change to UpperCase
 

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 ***

Copying Cell Conflict with Worksheet Change to UpperCase
 

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 ***