Hello, HELP!

Each time my program runs, it replaces an existing "REPORT" sheet with a new one. The program then writes VBA into the newly created sheet module. This program runs fine the first time (i.e. when there is no pre-existing "REPORT" sheet).

If there is a pre-existing "REPORT" sheet, the program explodes (and I am in the blast radius).

The problem is, I think, that the portion of the program that writes the code into the module sheet does not yet see or recognize that the new "REPORT" sheet exists yet (even though it actually does).

How can I overcome this situation and make it see the new sheet on the fly?

Thanks in advance.

quartz,

Usually, it is solved by starting off with something like

On Error Resume Next
Application.DisplayAlerts = False
Worksheets("REPORT").Delete
.....
'code to add the new REPORT sheet
.......
Application.DisplayAlerts = True

HTH,
Bernie
MS Excel MVP

"quartz" wrote in message
...
Hello, HELP!

Each time my program runs, it replaces an existing "REPORT" sheet
with a new one. The program then writes VBA into the newly created
sheet module. This program runs fine the first time (i.e. when there
is no pre-existing "REPORT" sheet).

If there is a pre-existing "REPORT" sheet, the program explodes (and
I am in the blast radius).

The problem is, I think, that the portion of the program that writes
the code into the module sheet does not yet see or recognize that the
new "REPORT" sheet exists yet (even though it actually does).

How can I overcome this situation and make it see the new sheet on
the fly?

Thanks in advance.

Bernie,

Thanks for responding, but actually my code is already structured similarly to the way you suggested.

I don't get a normal trappable error, I get a fatal error that causes Excel to shut down altogether.

Sometimes my code actually succeeds before it dies, other times, it doesn't write the code into the module when the error occurs.

Any other ideas???

quartz,

Sounds like it is time to:

1) Use Rob Bovey's code cleaner utility

2) If that doesn't work, start from a new workbook and rebuild your
application (unfortunately, it sounds like it is corrupted)

HTH,
Bernie
MS Excel MVP

"quartz" wrote in message
...
Bernie,

Thanks for responding, but actually my code is already structured
similarly to the way you suggested.

I don't get a normal trappable error, I get a fatal error that
causes Excel to shut down altogether.

Sometimes my code actually succeeds before it dies, other times, it
doesn't write the code into the module when the error occurs.

Any other ideas???

Rebuilt the file from scratch. Same behaviour.

Running the code inside the VBE does not generate any error.

Running it as the user would from the App generates the error.

quartz

Here's how I would do it. You can compare this to what you have

Sub MakeNewSheetCode()

Dim sh As Worksheet
Dim Vbc As VBComponent

On Error Resume Next
Application.DisplayAlerts = False
ThisWorkbook.Sheets("Report").Delete
Application.DisplayAlerts = True
On Error GoTo 0

Set sh = ThisWorkbook.Worksheets.Add
sh.Name = "Report"

Set Vbc = ThisWorkbook.VBProject.VBComponents(sh.CodeName)

With Vbc.CodeModule
.CreateEventProc "Change", "Worksheet"
.InsertLines .ProcBodyLine("Worksheet_Change", _
vbext_pk_Proc) + 1, "Call MySub"
End With


End Sub

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"quartz" wrote in message
...
Sorry,

I should have added that depending upon how I structure my code, I do get
a subscript error on the sheet, as though it doesn't exist or can't be
found. This error is incurred AFTER the program has already replaced the
sheet AND written data into it!

Again thanks for your assistance, I do hope you or someone posts back...