Please allow me to ask two questions:

(1) Which is the most fail-safe way to write in procedures: ThisWorkbook or
ActiveWorkbook?

Moreover, assuming it is ThisWorkbook, would I be required to write
Dim mySheet As Worksheet
Set mySheet= ThisWorkbook.Sheets(1)

Or can I simply write
Set mySheet=Sheets(1)

(2) I have a Clear Forms button on my toolbar that makes cell.Value="" on a
number of different sheets. Can I include Application.EnableEvents at top
and bottom as such,
Sub ClearForms()
Application.EnableEvents=False
With Sheets(1)
[code to clear cells]
End With
[additional With statements]
Application.EnableEvents=True
End Sub
or,
is it better to include it for each sheet action as such,

Sub ClearForms()
With Sheets(1)
Application.EnableEvents=False
[code to clear cells]
Application.EnableEvents=True
End With
[additional With statements]
End Sub

I'm just trying to make my application as bullet-proof against Excel quirks
as possible (if there is such a thing). Thank you in advance.

Jim Kobzeff

"JK" wrote in message news:1zW8e.17644$Zn3.8439@trnddc02...
Please allow me to ask two questions:

(1) Which is the most fail-safe way to write in procedures: ThisWorkbook
or
ActiveWorkbook?

It depends. If you want to refer to the workbook that the executing coede is
in, use ThisWorkbook. If you want to refer to the currently activated
workbook, which could be another workbook if say you open a workbook, then
use ActiveWorkbook.

Moreover, assuming it is ThisWorkbook, would I be required to write
Dim mySheet As Worksheet
Set mySheet= ThisWorkbook.Sheets(1)

Yes

Or can I simply write
Set mySheet=Sheets(1)

.... as this will default to the Activeworkbook.

(2) I have a Clear Forms button on my toolbar that makes cell.Value="" on
a
number of different sheets. Can I include Application.EnableEvents at top
and bottom as such,
Sub ClearForms()
Application.EnableEvents=False
With Sheets(1)
[code to clear cells]
End With
[additional With statements]
Application.EnableEvents=True
End Sub
or,
is it better to include it for each sheet action as such,

Sub ClearForms()
With Sheets(1)
Application.EnableEvents=False
[code to clear cells]
Application.EnableEvents=True
End With
[additional With statements]
End Sub

Best to add error handling in case something goes wrong, don't leave
Application.EnableEvents off

Sub ClearForms()
On Error Goto cf_exit:
Application.EnableEvents=False
With Sheets(1)
[code to clear cells]
End With
[additional With statements]
cf_exit:
Application.EnableEvents=True
End Sub

JK,

You didn't ask, but one thing you could do to shorten your code, would be to
specify all the sheets in an array, something along these lines:

Sub test()
Dim ws As Worksheet

For Each ws In Worksheets(Array("Sheet1", "Sheet2"))
ws.Range("A1").ClearContents
Next ws
End Sub

Of course add in error handling as Bob showed before and after the For/Next.

hth,

Doug

"JK" wrote in message news:1zW8e.17644$Zn3.8439@trnddc02...
Please allow me to ask two questions:

(1) Which is the most fail-safe way to write in procedures: ThisWorkbook
or
ActiveWorkbook?

Moreover, assuming it is ThisWorkbook, would I be required to write
Dim mySheet As Worksheet
Set mySheet= ThisWorkbook.Sheets(1)

Or can I simply write
Set mySheet=Sheets(1)

(2) I have a Clear Forms button on my toolbar that makes cell.Value="" on
a
number of different sheets. Can I include Application.EnableEvents at top
and bottom as such,
Sub ClearForms()
Application.EnableEvents=False
With Sheets(1)
[code to clear cells]
End With
[additional With statements]
Application.EnableEvents=True
End Sub
or,
is it better to include it for each sheet action as such,

Sub ClearForms()
With Sheets(1)
Application.EnableEvents=False
[code to clear cells]
Application.EnableEvents=True
End With
[additional With statements]
End Sub

I'm just trying to make my application as bullet-proof against Excel
quirks
as possible (if there is such a thing). Thank you in advance.

Jim Kobzeff

Thank you for both answers. But, which is more apt to avoid conflicts,
ActiveWorkbook or ThisWorkbook?

Thank you.
Jim Kobzeff

"JK" wrote in message news:1zW8e.17644$Zn3.8439@trnddc02...
Please allow me to ask two questions:

(1) Which is the most fail-safe way to write in procedures: ThisWorkbook
or ActiveWorkbook?

Moreover, assuming it is ThisWorkbook, would I be required to write
Dim mySheet As Worksheet
Set mySheet= ThisWorkbook.Sheets(1)

Or can I simply write
Set mySheet=Sheets(1)

(2) I have a Clear Forms button on my toolbar that makes cell.Value="" on
a number of different sheets. Can I include Application.EnableEvents at
top and bottom as such,
Sub ClearForms()
Application.EnableEvents=False
With Sheets(1)
[code to clear cells]
End With
[additional With statements]
Application.EnableEvents=True
End Sub
or,
is it better to include it for each sheet action as such,

Sub ClearForms()
With Sheets(1)
Application.EnableEvents=False
[code to clear cells]
Application.EnableEvents=True
End With
[additional With statements]
End Sub

I'm just trying to make my application as bullet-proof against Excel
quirks as possible (if there is such a thing). Thank you in advance.

Jim Kobzeff

99% of the time this workbook is what you want to use. It refers to the
workbook that is executing the code. You only need to use activeworkbook if
you want to refer to some other work book besides the one executing the code.

That being said very often you see people using activeworkbook in place of
thisworkbook, because the book executing the code is the active workbook.
Technically it is not correct to do this but under most circumstances it will
work out just fine.

The long and the short of it is use thisworkbook unless you have a good
reason not to.

HTH

"JK" wrote:

Thank you for both answers. But, which is more apt to avoid conflicts,
ActiveWorkbook or ThisWorkbook?

Thank you.
Jim Kobzeff

"JK" wrote in message news:1zW8e.17644$Zn3.8439@trnddc02...
Please allow me to ask two questions:

(1) Which is the most fail-safe way to write in procedures: ThisWorkbook
or ActiveWorkbook?

Moreover, assuming it is ThisWorkbook, would I be required to write
Dim mySheet As Worksheet
Set mySheet= ThisWorkbook.Sheets(1)

Or can I simply write
Set mySheet=Sheets(1)

(2) I have a Clear Forms button on my toolbar that makes cell.Value="" on
a number of different sheets. Can I include Application.EnableEvents at
top and bottom as such,
Sub ClearForms()
Application.EnableEvents=False
With Sheets(1)
[code to clear cells]
End With
[additional With statements]
Application.EnableEvents=True
End Sub
or,
is it better to include it for each sheet action as such,

Sub ClearForms()
With Sheets(1)
Application.EnableEvents=False
[code to clear cells]
Application.EnableEvents=True
End With
[additional With statements]
End Sub

I'm just trying to make my application as bullet-proof against Excel
quirks as possible (if there is such a thing). Thank you in advance.

Jim Kobzeff

Jim,

It depends. Neither avoids conflict if you use the wrong one. It is all down
to a question of design, hard and fast answers are not appropriate.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"JK" wrote in message news:QmX8e.18207$ox3.5936@trnddc03...
Thank you for both answers. But, which is more apt to avoid conflicts,
ActiveWorkbook or ThisWorkbook?

Thank you.
Jim Kobzeff

"JK" wrote in message news:1zW8e.17644$Zn3.8439@trnddc02...
Please allow me to ask two questions:

(1) Which is the most fail-safe way to write in procedures: ThisWorkbook
or ActiveWorkbook?

Moreover, assuming it is ThisWorkbook, would I be required to write
Dim mySheet As Worksheet
Set mySheet= ThisWorkbook.Sheets(1)

Or can I simply write
Set mySheet=Sheets(1)

(2) I have a Clear Forms button on my toolbar that makes cell.Value=""
on
a number of different sheets. Can I include Application.EnableEvents at
top and bottom as such,
Sub ClearForms()
Application.EnableEvents=False
With Sheets(1)
[code to clear cells]
End With
[additional With statements]
Application.EnableEvents=True
End Sub
or,
is it better to include it for each sheet action as such,

Sub ClearForms()
With Sheets(1)
Application.EnableEvents=False
[code to clear cells]
Application.EnableEvents=True
End With
[additional With statements]
End Sub

I'm just trying to make my application as bullet-proof against Excel
quirks as possible (if there is such a thing). Thank you in advance.

Jim Kobzeff