Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2002/Windows XP - PageSetup error
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2002/Windows XP - PageSetup error
Whoops, forgot to proofread... Obviously I'm using
Workbook_SheetActivate, not "Worksheet_BeforeActivate", which of course does not exist! Matt |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2002/Windows XP - PageSetup error
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 wrote in message oups.com... Whoops, forgot to proofread... Obviously I'm using Workbook_SheetActivate, not "Worksheet_BeforeActivate", which of course does not exist! Matt |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2002/Windows XP - PageSetup error
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2002/Windows XP - PageSetup error
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 wrote in message oups.com... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2002/Windows XP - PageSetup error
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...b;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 wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2002/Windows XP - PageSetup error
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 wrote in message oups.com... 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...b;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 wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel 2002 problems in windows 7? | Excel Discussion (Misc queries) | |||
Arrow keys in Excel 2002/running Windows XP | Setting up and Configuration of Excel | |||
Error arising in Windows XP having Excel 2002, was fine in WindowsNT having Excel2000 | Excel Programming | |||
pagesetup 1004 error | Excel Programming | |||
problem in excel 2002 and windows xp | Excel Programming |