New version of spreadsheet ignores printer PINs???

[ianpwilliams]

I've been working on a new version of an XLS spreadsheet at work (there is v7 and v8, and I'm working on v9). Everything is going well, apart from a rather strange issue.

We use printer PINs in work, so when we send some stuff to the printer, the print job is held in personal storage, and is printed when we go to the printer, type in our PIN, and retrieve the print job.

All versions of the spreadsheet have "Print All" buttons on them, which print certain worksheets all in one go. In v7 and v8, everything works fine. You can print using one of the "Print All" buttons, or via the Office button, and if you have a PIN set up, then nothing will print until you release the print job. But in v9, if you print using any of the above methods, the print job will print immediately, regardless of whether you have a PIN set up or not.

I've checked the code for the "Print All" buttons in v9, and the code is exactly the same, it's just that the array command is extended to cover a few new worksheets that have been added. And if it's printing immediately from the Office button too, then it seems like it can't be a VBA error anyway.

Does anyone know why this could be happening? The file format is still the same (XLS), and I haven't changed the fundamental setup of the spreadsheet in any way. But it could be a real issue, because we have to use printer PINs in work due to data protection, so if I can't fix this, then it may be that v9 can never be used.

[TMS]

I have no idea how your printer works in relation to requiring a PIN so this is just a shot in the dark. OK?

If your data is stored in "personal storage", then I'm guessing there is an ID associated with that storage which, in turn, requires a PIN. So, the guess is that, either you are somehow sending data to the printer with an ID that either doesn't have a PIN, or it has been stored somehow, or it is some other ID that doesn't ned a PIN.

Or the printer has been configured for straight through printing for some IDs. Or the security has been disabled.

All hypothetical but just some ideas to check out.

Regards, TMS

[ianpwilliams]

It's such a strange one. The printers in work are all on a network, and everybody uses their own PINs (set up through Windows on each PC). The PIN printing works for absolutely everything except my spreadsheet, and even works for the earlier versions of my spreadsheet. And all I've done is add a few worksheets and VBA modules, and in the case of the "Print All" button VBA modules, I just modified the existing code to include the new worksheets.

So I have no idea what can be causing the problem, because I don't know where to begin!

[ianpwilliams]

Wow, just found this:

http://support.microsoft.com/kb/829766

I wasn't using a printer PIN (naughty me) at the time I emailed it, so if I try adding a printer PIN and then following the steps above, then maybe it might work!

I had no idea that Excel stored printer settings!

[TMS]

Thanks for sharing the link.


If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[ianpwilliams]

It's a odd one. I tried changing the printer settings, then cancelling, and saving. It will now print allowing for the PIN when using the Print option through the Office button, but it won't allow for the PIN when using one of the buttons which trigger the VBA code (although it did randomly do it once). It sounds like something I will have to keep trying, and hopefully it will work eventually.

Thanks for the help, hopefully someone might find the link useful.

[ianpwilliams]

I've cracked it now. It turns out that not only does an Excel spreadsheet remember what your printer settings were, it remembers your printer settings for EVERY worksheet. So if you don't have your print settings set to print with a PIN for a particular worksheet, then any buttons that print that are on that worksheet won't print with a PIN.

Hopefully this might be useful information for others, because it took me a while to figure out how it worked!

[TMS]

Thanks again, Ian. I'm sure it will be helpful should anyone else encounter this problem.

Regards, TMS