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