Excel 2002/Windows XP - PageSetup error

[mattkahne]

Hi there,

My company is in the process of gradually replacing employees'
computers, going from Windows 2000 to XP (not sure which edition of XP,
I don't have one of the new machines yet). Regardless of Windows
version, everyone seems to be running Excel 2002 SP3.

I have a multi-user (single copy, opened read-only) Excel workbook to
which I recently added code to set part of the page header as each
sheet is opened. This is done in the ThisWorkbook module using the
function Worksheet_BeforeActivate:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Sh.PageSetup.RightHeader = [what we want RightHeader to say]
End Sub

So far this has worked fine, including for a few users who are running
Excel 2002 on Windows XP. However, in setting up a new user's machine
today, our IT department found they were getting an error when this
code executed, along the lines of "Run-time error 1004: Unable to set
the RightHeader property of the PageSetup class".

I've searched but haven't found anything to suggest a reason why this
would work on some computers and not others... I don't see anything in
the options or settings jumping out at me... Macro security has to be
at medium (or low, but we discourage that) for the program itself to
work (navigation is done via macro buttons and hyperlinks) so that has
to be consistent across the board... There are no missing references in
the VBA on the machine where the error is occurring... IT even wiped
and reinstalled everything on the machine with the error.

Any suggestions would be most appreciated... Thank you!

Matt

 

[mattkahne]

Whoops, forgot to proofread... Obviously I'm using
Workbook_SheetActivate, not "Worksheet_BeforeActivate", which of course
does not exist!

Matt

 

[Jim Cone]

Matt,

A couple of thoughts...
Is there a chart sheet somewhere in the workbook?
Is the "what we want RightHeader to say" a simple string or are you
for instance trying to read information from the user's PC?

Regards,
Jim Cone
San Francisco, USA

 

[mattkahne]

Hi Jim,

To answer your questions:

-No chart sheets in the workbook, just spreadsheets.

-It's not quite a simple string as it does include some formatting and
a call to a function that retrieves the user's network ID (can be found
by googling "fOSUserName", that's how I found it a couple of years
ago).

If this is important I can post the details (the fOSUserName function,
and the exact string I'm setting the header to) from the office
tomorrow as I don't have them handy here at home. However, among the
things I tested when I was able to use the machine where the error
occurred was replacing the header text with a simple string, and the
same error occurred when the VBA code tried to set the RightHeader.

For what it's worth I was also able to test using my own ID and another
employee's ID (I have write access on the directory where the workbook
is housed, most other users have read-only access because they're not
saving the workbook, just using the spreadsheets, printing, and
closing). Again, no effect -- a computer generating the error for one
user ID generated it for both, and a computer not generating the error
was fine for both IDs.

Basically, I'm putting the individual user's network ID in the print
header because there are many people using these spreadsheets and they
sometimes have trouble separating out their own printouts when several
pile up on the printer.

Thanks for your consideration!

Matt

 

[Jim Cone]

Matt,

You are ahead of me, as you have already tested it
using a string only. So it shouldn't be the userID that is
causing the problem.
As a temporary? crutch, you could put an on error resume next
statement in the code. The printout without a name would
belong to the computer with the problem.

You might want to check if it will work if you put the
code into the Workbook_BeforePrint event.

Also, for what's it worth, I normally use the Excel property...
"Application.UserName" to identify worksheet print outs.
Doubt if I can contribute anything more to this.

Regards,
Jim Cone
San Francisco, USA

 

[mattkahne]

Hi Jim,

Problem solved! It wasn't an issue with the spreadsheet after all, but
rather an MS "issue" that I wasn't aware of... I didn't think to search
MSDN before posting here as I should have.

The issue was that because IT was setting up a brand new machine, they
hadn't yet installed any printer drivers before setting up a shortcut
to my workbook and trying to open it... And setting PageSetup elements
without printer drivers installed is a no-no:

http://support.microsoft.com/default.aspx?scid=kb;en-us;291298

So this issue had absolutely nothing to do with upgrading from Win2000
to WinXP, it was just an "order of operations" thing down in IT and
could just as easily have happened 3 months ago setting up a new
Win2000 machine.

Thanks for your time Jim, much appreciated!

Matt

 

[Jim Cone]

Matt,

Thanks for the information. Glad that one is solved.
It reinforces my belief that 99% of the printing problems
reported in the newsgroups can be cured by updating the
printer drivers. Just wish I had paid attention.<g>

Regards,
Jim Cone
San Francisco, USA