New Excel Sheet Not Appearing In Project Explorer 2

[Sheffield]

Greetings,

I have a application that...
(a.) opens an existing Excel WorkBook,
(b.) adds a new WorkSheet (sheet4) and
(c.) populates sheet4 with data.

My Problem: when I open the Visual Basic editor and look at the Project Explorer, Sheet4 isn't visible. Moreover, if I attempt to reference Sheet4 in my VBA code, a 424 error (object required) is raised.

HOWEVER, if I click on the 'ThisWorkBook' object within the Project Explorer, Sheet4 is visible and the program functions correctly.

Unfortunately, I cannot have a user jump through that kind of hoop to get the program to work.

Does anybody know how to enable the WorkBook to 'refresh' (for lack of a better word) and see that a sheet4 actually exists?

I'm totally clueless here. I greatly appreciate any and all help anyone can provide.

Thanks

 

[TonyJollans]

Hi Sheffield,

I suspect what you are doing is something like ..

[tt] ActiveWorkbook.Sheets.Add
Sheet4.Select[/tt]

Here, Sheet4 (if that's its default name) doesn't exist at compile time and so the reference isn't resolved; the compiler treats it as a variable which will be valid when it's needed. Then, at run time, it isn't a variable and it isn't valid and there is nothing to do but raise an error. You can avoid this by referencing the new sheet in a way which will be resolved at run time ..

[tt] ActiveWorkbook.Sheets.Add
ActiveWorkbook.Sheets("Sheet4").Select[/tt]

I cannot explain the other symptoms you describe from the information you've given. Can you give a bit more detail about your code and where it is and what symptoms the User sees - as I wouldn't normally expect the User to care about the VBE.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[PaulRD]

Sheffield,
If I understand the post, the problem is you cant run you procedure because the sheet does not exist according to the project explorer. Try using this before executing your procedure

Application.OnTime Now, procedure:="your procedure"

OnTime should allow the sheet to "appear" before executing you next code.

I hope this helps