Getting VBE to recognize a new sheet

[Guest]

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 Deitrick]

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

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).

 

[Guest]

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

 

[Bernie Deitrick]

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

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.

 

[Guest]

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.

 

[Dick Kusleika]

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.

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!