[Phrank]

Hi,

I've got an overflow error that I don't know how to deal with. High
level: I'm clearing all contents and formats from a sheet, and then
copying/pasting all (updated) contents and formats from another
workbook/sheet (first section of code below) The Target worksheet
(the one being cleared and pasted into) has Worksheet_Change code that
I've just added (second bit of code below). When the cells on the
Target sheet are cleared, it triggers the Worksheet_Change event, and
I get the overflow error. Any thoughts as to why, and more
importantly, how to deal with this? Thanks!

With wksSource
wksTarget.Cells.ClearContents
wksTarget.Cells.Interior.Pattern = xlNone
.Cells.Copy wksTarget.Cells(1)
End With 'wksSource


Private Sub Worksheet_Change(ByVal Target As Range)

Dim iCol As Long
Dim iRow As Long
Dim myNotes As String

If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub

[more code here, but it dies at the first line]

Frank

[GS[_6_]]

Try...

With wksTarget
.UsedRange.Clear
wksSource.UsedRange.Copy .Cells(1)
End With 'wksSource

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

[GS[_6_]]

Oops.., forgot most important part...

With Application
.EnableEvents = False: ScreenUpdating = False
wksTarget.UsedRange.Clear
wksSource.UsedRange.Copy wksTarget.Cells(1)
.EnableEvents = True: ScreenUpdating = True
End With 'Application

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

[Phrank]

Hi Garry,

Thanks for this! I plugged it into the Worksheet_Change module, and
I'm getting a Run Timer Error 424 'Object required' error message at
the wksTarget.UsedRange.Clear line. Should this code go somewhere
else?

Private Sub Worksheet_Change(ByVal Target As Range)

Dim iCol As Long
Dim iRow As Long
Dim myNotes As String

With Application
.EnableEvents = False: ScreenUpdating = False
wksTarget.UsedRange.Clear
wksSource.UsedRange.Copy wksTarget.Cells(1)
.EnableEvents = True: ScreenUpdating = True
End With 'Application


On Sun, 05 Jun 2016 11:13:03 -0400, GS wrote:

Oops.., forgot most important part...

With Application
.EnableEvents = False: ScreenUpdating = False
wksTarget.UsedRange.Clear
wksSource.UsedRange.Copy wksTarget.Cells(1)
.EnableEvents = True: ScreenUpdating = True
End With 'Application

[GS[_6_]]

Hi Garry,

Thanks for this! I plugged it into the Worksheet_Change module, and
I'm getting a Run Timer Error 424 'Object required' error message at
the wksTarget.UsedRange.Clear line. Should this code go somewhere
else?

Private Sub Worksheet_Change(ByVal Target As Range)

Dim iCol As Long
Dim iRow As Long
Dim myNotes As String

With Application
.EnableEvents = False: ScreenUpdating = False
wksTarget.UsedRange.Clear
wksSource.UsedRange.Copy wksTarget.Cells(1)
.EnableEvents = True: ScreenUpdating = True
End With 'Application

This is based on your original code where I assume you have 'Set' a ref
to wksSource and wksTarget in a standard module procedure. That's where
this code goes, NOT in your Worksheet_Change event! The point of this
is so that sheet event code doesn't fire while you're updating the
contents of the worksheet. Since you didn't post ALL the code, it's
difficult to determine what it's doing. Obviously, you are running a
procedure that clears any content/format in the target sheet before
populating it with content/format of the source sheet. What the event
code does is not clear, nor is why it exists.

In a standard module...

Sub TransferData()
Dim wksSource As Worksheet, wksTarget As Worksheet
Set wksSource = Workbooks("Filename").Sheets("Sheetname")
Set wksTarget = ThisWorkbook.Sheets("Sheetname")

With Application
.EnableEvents = False: ScreenUpdating = False
wksTarget.UsedRange.Clear
wksSource.UsedRange.Copy wksTarget.Cells(1)
.EnableEvents = True: ScreenUpdating = True
End With 'Application
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

[Phrank]

On Mon, 06 Jun 2016 00:19:12 -0400, GS wrote:

Hi Garry,

Thanks for this! I plugged it into the Worksheet_Change module, and
I'm getting a Run Timer Error 424 'Object required' error message at
the wksTarget.UsedRange.Clear line. Should this code go somewhere
else?

Private Sub Worksheet_Change(ByVal Target As Range)

Dim iCol As Long
Dim iRow As Long
Dim myNotes As String

With Application
.EnableEvents = False: ScreenUpdating = False
wksTarget.UsedRange.Clear
wksSource.UsedRange.Copy wksTarget.Cells(1)
.EnableEvents = True: ScreenUpdating = True
End With 'Application

This is based on your original code where I assume you have 'Set' a ref
to wksSource and wksTarget in a standard module procedure. That's where
this code goes, NOT in your Worksheet_Change event! The point of this
is so that sheet event code doesn't fire while you're updating the
contents of the worksheet. Since you didn't post ALL the code, it's
difficult to determine what it's doing. Obviously, you are running a
procedure that clears any content/format in the target sheet before
populating it with content/format of the source sheet. What the event
code does is not clear, nor is why it exists.

In a standard module...

Sub TransferData()
Dim wksSource As Worksheet, wksTarget As Worksheet
Set wksSource = Workbooks("Filename").Sheets("Sheetname")
Set wksTarget = ThisWorkbook.Sheets("Sheetname")

With Application
.EnableEvents = False: ScreenUpdating = False
wksTarget.UsedRange.Clear
wksSource.UsedRange.Copy wksTarget.Cells(1)
.EnableEvents = True: ScreenUpdating = True
End With 'Application
End Sub


Hi Garry. Thanks for the explanation. This worked beautifully. And I
now understand what you are saying, and I see this is a replacement fo
rmy other copy cells code. I've also got a few lines of code that run
at the beginning and end of my larger modules that turn off screen
updating and calculations, and when I saw the EnableEvents and
ScreenUpdating line, I just thought this needed to go there. I'm good
to go for this piece of my puzzle. Thank you!!

[GS[_6_]]

Hi Garry. Thanks for the explanation. This worked beautifully. And
I
now understand what you are saying, and I see this is a replacement
fo
rmy other copy cells code. I've also got a few lines of code that
run
at the beginning and end of my larger modules that turn off screen
updating and calculations, and when I saw the EnableEvents and
ScreenUpdating line, I just thought this needed to go there. I'm
good
to go for this piece of my puzzle. Thank you!!

You're very welcome!

FWIW
Here's how I handle enabling/disabling fast code. It uses a global Type
to manage which procedures turn things off/on so you don't run into
unexpected issues...

'--------------------------------------------------------------------------------------
' **Note: EnableFastCode requires the following declarations be in a
standard module.
'--------------------------------------------------------------------------------------
'Type udtAppModes
' 'Default types
' Events As Boolean: CalcMode As XlCalculation: Display As Boolean:
CallerID As String
' 'Project-specific types
'End Type
'Public AppMode As udtAppModes
'--------------------------------------------------------------------------------------
Sub EnableFastCode(Caller$, Optional SetFast As Boolean = True)
' **Note: Requires 'Type udtAppModes' and 'Public AppMode As
udtAppModes' declarations

'The following will make sure only the Caller has control,
'and allows any Caller to take control when not in use.
If AppMode.CallerID < Caller Then _
If AppMode.CallerID < "" Then Exit Sub

With Application
If SetFast Then
AppMode.Display = .ScreenUpdating: .ScreenUpdating = False
AppMode.CalcMode = .Calculation: .Calculation =
xlCalculationManual
AppMode.Events = .EnableEvents: .EnableEvents = False
AppMode.CallerID = Caller
Else
.ScreenUpdating = AppMode.Display
.Calculation = AppMode.CalcMode
.EnableEvents = AppMode.Events
AppMode.CallerID = ""
End If
End With
End Sub

...and here's how to set your projects up to use it...

Sub DoTask()
Const sSource$ = "DoTask"

EnableFastCode sSource
'do intensive stuff
EnableFastCode sSource, False
End Sub

...where sSource holds the name of the procedure (Sub or Function) that
would use this. If already in use by another procedure, EnableFastCode
will not get toggled.

Note:
The const sSource is also used for error logging and so is
multi-purpose in my projects!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

[Phrank]

On Tue, 07 Jun 2016 11:40:23 -0400, GS wrote:

Hi Garry. Thanks for the explanation. This worked beautifully. And
I
now understand what you are saying, and I see this is a replacement
fo
rmy other copy cells code. I've also got a few lines of code that
run
at the beginning and end of my larger modules that turn off screen
updating and calculations, and when I saw the EnableEvents and
ScreenUpdating line, I just thought this needed to go there. I'm
good
to go for this piece of my puzzle. Thank you!!

You're very welcome!

FWIW
Here's how I handle enabling/disabling fast code. It uses a global Type
to manage which procedures turn things off/on so you don't run into
unexpected issues...

'--------------------------------------------------------------------------------------
' **Note: EnableFastCode requires the following declarations be in a
standard module.
'--------------------------------------------------------------------------------------
'Type udtAppModes
' 'Default types
' Events As Boolean: CalcMode As XlCalculation: Display As Boolean:
CallerID As String
' 'Project-specific types
'End Type
'Public AppMode As udtAppModes
'--------------------------------------------------------------------------------------
Sub EnableFastCode(Caller$, Optional SetFast As Boolean = True)
' **Note: Requires 'Type udtAppModes' and 'Public AppMode As
udtAppModes' declarations

'The following will make sure only the Caller has control,
'and allows any Caller to take control when not in use.
If AppMode.CallerID < Caller Then _
If AppMode.CallerID < "" Then Exit Sub

With Application
If SetFast Then
AppMode.Display = .ScreenUpdating: .ScreenUpdating = False
AppMode.CalcMode = .Calculation: .Calculation =
xlCalculationManual
AppMode.Events = .EnableEvents: .EnableEvents = False
AppMode.CallerID = Caller
Else
.ScreenUpdating = AppMode.Display
.Calculation = AppMode.CalcMode
.EnableEvents = AppMode.Events
AppMode.CallerID = ""
End If
End With
End Sub

..and here's how to set your projects up to use it...

Sub DoTask()
Const sSource$ = "DoTask"

EnableFastCode sSource
'do intensive stuff
EnableFastCode sSource, False
End Sub

..where sSource holds the name of the procedure (Sub or Function) that
would use this. If already in use by another procedure, EnableFastCode
will not get toggled.

Note:
The const sSource is also used for error logging and so is
multi-purpose in my projects!

I am DEFINITELY going to plug that in mine and see how it improves the
timing. And there's another guy I collaborate with at work that I'm
sure will find this extremely useful. Thank you VERY much for taking
that additional time to add that and help educate me. ...people like
you on these forums never cease to amaze me with their (your)
generosity of time and patience - I was a technical training
instructor in the US Air Force for several years, and I know it takes
patience to teach. :) Thank you!

Frank

[GS[_6_]]

I am DEFINITELY going to plug that in mine and see how it improves
the
timing. And there's another guy I collaborate with at work that I'm
sure will find this extremely useful. Thank you VERY much for taking
that additional time to add that and help educate me. ...people like
you on these forums never cease to amaze me with their (your)
generosity of time and patience - I was a technical training
instructor in the US Air Force for several years, and I know it takes
patience to teach. :) Thank you!

My Pleasure!
FWIW, I taught apprenticeship training (retired now) for our Ministry
of Labour in the Transportation & Industrial Power fields at the
technical division of a community college in my hometown. I know how
easy it is to get frustrated with people who really shouldn't be where
they're messing about. Excel is daunting enough to get folks into
trouble very easily so it's nice to hear/know I'm helping others to
grow their skills!!! (Just reciprocating what was given<g)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus