FreezePanes in Excel called from MS Project by VBA

[SoftwareTester]

While exporting data from MS Project to Excel using VBA I create a new
worksheet in Excel and add data to it.
I have a row containing descriptions of the columns and all tasks in the
rows below this header. I want to create panes in order to keep the
taskdescription visible (plus the header) and being able to scroll the
columns containing the taskinfo.

I want to create and freezepanes on that worksheet I created

How can I do that?

 

[John]

While exporting data from MS Project to Excel using VBA I create a new
worksheet in Excel and add data to it.
I have a row containing descriptions of the columns and all tasks in the
rows below this header. I want to create panes in order to keep the
taskdescription visible (plus the header) and being able to scroll the
columns containing the taskinfo.

I want to create and freezepanes on that worksheet I created

How can I do that?

SoftwareTester,
Since you've been able to get this far with your VBA code, just a little
more homework and you'll be there. Here are a couple of suggestions to
find the appropriate code syntax.

Open Excel and record a macro while you freeze the panes you want. Then
translate that code to your Project VBA code.

Or, open the VBA object browser and search the Excel object library to
find the syntax for the FreezePanes method.

Hope this helps.

John
Project MVP

 

[SoftwareTester]

I tried that BEFORE I posted the question here.

So I selected the cell I want to use for creating panes and recorded

Range("H4").Select
ActiveWindow.FreezePanes = True

so I added
xlSheet.Range("H4").Select
ActiveWindow.FreezePanes = True

but that created a crash (into the debugger) while running from MS project.

After the reply by John I tried again and became a bit more smart changing
the code above into
xlSheet.Range("H4").Select
Excel.ActiveWindow.FreezePanes = True

This worked.

 

[Rod Gill]

You tried to freeze panes in Project! You need to add:
xlApp. ActiveWindow.FreezePanes = True

where xlApp is your Excel Application Object, or use:
xlSheet.Application. ActiveWindow.FreezePanes = True


--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:
http://www.projectvbabook.com

I tried that BEFORE I posted the question here.

So I selected the cell I want to use for creating panes and recorded

Range("H4").Select
ActiveWindow.FreezePanes = True

so I added
xlSheet.Range("H4").Select
ActiveWindow.FreezePanes = True

but that created a crash (into the debugger) while running from MS
project.

After the reply by John I tried again and became a bit more smart changing
the code above into
xlSheet.Range("H4").Select
Excel.ActiveWindow.FreezePanes = True

This worked.



__________ Information from ESET Smart Security, version of virus
signature database 4471 (20090930) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4471 (20090930) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[John]

I tried that BEFORE I posted the question here.

So I selected the cell I want to use for creating panes and recorded

Range("H4").Select
ActiveWindow.FreezePanes = True

so I added
xlSheet.Range("H4").Select
ActiveWindow.FreezePanes = True

but that created a crash (into the debugger) while running from MS project.

After the reply by John I tried again and became a bit more smart changing
the code above into
xlSheet.Range("H4").Select
Excel.ActiveWindow.FreezePanes = True

This worked.

SoftwareTester,
Good. I'm glad I was able to prod you into looking a bit deeper. Good
luck with your VBA.

John
Project MVP