AppClassModule problems.

[Turpis]

I have a problem that stumps me. I have provided enough code for you see what is happening. This code is in an add-in. The code works perfectly on: Win2000/Off2000, Win2000/OffXP, and on a virtual machine WinXP/Off2000, but the new computers from Dell that we got have WinXP/Off2000 and will not run the Workbook_BeforePrint at all. I have a virtual machine setup exactly the same as the Dell's and it works fine. I have put msgboxes in the Private Sub Workbook_BeforePrint of the add-in and no msgbox appears. What could possible be going on here...I will considered all theories at this point, including aliens are interfering with my code "because it is fun".

-------------------
in ThisWorkbook
-------------------
Private Sub Workbook_BeforePrint(Cancel As Boolean)
InitializeApp
End Sub

Private Sub Workbook_Open()
InitializeApp
End Sub
-------------------
in a module
-------------------
Dim x As New AppEventClassModule

Sub InitializeApp()
Set x.App = Application
End Sub
-------------------
in a ClassModule
-------------------
Public WithEvents App As Application

Private Sub App_WorkbookBeforePrint(ByVal Wb As Workbook, Cancel As Boolean)
Dim ans, ans2, ans3

duh1 = Wb.Name
duh2 = Left(duh1, 8)
CMName = Application.UserName

If Not duh2 = "report_2" Then
GoTo theEnd
End If
Select Case CMName
Case "jcollins"
GoTo pRentSR
Case "jyocum"
GoTo pRentAW
Case "lshannon"
GoTo pRentBoth
Case "sgray"
GoTo pRentAW
Case Else
GoTo theEnd
End Select ' thus ends this case section
' this section fires off an email to susan and others
pRentSR:
ans = MsgBox("Do you wish to email Ship Sheet?", vbYesNo, "Email Question&quot
If ans = vbYes Then
GoTo emailshipsheetSR
End If

ans3 = MsgBox("Would you like to print a copy?", vbYesNo, "Print Question&quot
If Not ans3 = vbYes Then
Cancel = True
End If
GoTo theEnd

emailshipsheetSR:
ActiveSheet.Copy

blah, blah, blah...you get the idea
-----------------

Charles
Walden's Machine, Inc.
Quality/Office Developer

 

[Turpis]

I forgot to mention that this is Excel.

Charles

 

[RobBroekhuis]

Have you tried in a simple dummy workbook to see if you can get the regular workbook_beforeprint to fire? I don't understand the purpose of the workbook_beforeprint event sub in your add-in - would you ever be printing the add-in itself?
Rob

 

[Turpis]

Yes, I have tested the regular workbook_beforeprint. It fires as part of thisworkbook for the dummy workbook, but if I have the code in an add-in which I need it to be, it won't do anything. I had threads in the past about how to get this to happen for workbook_opens in add-ins and using that initializeapp in the thisworkbook portion of the add-in worked for both the open and the print. I have since tested and noticed that I only need it for the open (so that the formatting code waits for the activeworkbook to open before firing. The print however does not. I have not bothered to change the code simply because it has always worked. I have tested this program on every computer in our company and it works on all of them except the latest batch of dell's that we just got.

It doesn't print the add-in itself at all. The code is just in the add-in on the network so that I only have to change the code in it and all 50 users are effected. The code is designed to intercept the print command and fire off emails to various people in the company and then lets the user print if they want to. Like I said, this program has worked error free for 3 months and now just the intercepting print function just won't work (on the new dell's)...all the other code does however.

Baffeled,
Charles
Quality/Office Developer

 

[Turpis]

Perhaps, you know of another way to monitor workbook events from an add-in.

Charles

 

[RobBroekhuis]

This is a long shot, but have you checked if
application.enableevents
is true on the troubled computers? There must be something else about the operating system or office environment for these computers...
Rob

 

[Turpis]

I am not sure where that application.enableevents is. I have been working on a workaround to the problem for most of the afternoon. Since the subs all seem to work just fine and so does the initial workbookopen in the add-in, I am just going to assign the code behind the printbuttons at startup with my sub for emailing and printing and then reset the buttons at close (if that event will work). But if you could learn me on this application.enableevents and its use, I might investigate that.

 

[RobBroekhuis]

If some events work fine, then don't bother. If application.enableevents is false, then no events will fire. It's a manual setting, so unless it's turned off by one of your subs, it would never be false. That's why I considered it a long shot.
Rob

 

[combo]

What do you do with the add-in?
A standard way is to save workbook as an add-in in the folder excel suggests. Next you can install/uninstall it using Tools>Addins dialog.

If you want to distribute this add-in you need to copy it to the directory excel keeps add-ins or wherever else. Next it must be installed - if it is in the default directory only by checking the Tools>Addins dialog, if not - also by selecting its place. Without that it will not be open and events activated. If it is installed, excel opens it silently in the beginning.
At that moment Workbook_Open add-in's macro in ThisWorkbook module fires and activates Application events defined in class module.

As for the Workbook_BeforePrint sub in add-in's ThisWorkbook module - Rob is right, it is unnecessary, as you will never print an add-in and this event never fire.

 

[Turpis]

Thanks Combo, but the add-in is installed by registry magic by my network guy and resides on the network itself and not in their add-ins folder. That way I only have to modify one .xla and it effects everybody. Everybody in a certain group has it installed and registered and I am a trusted source so they never even know that it is there.

As to your question of what this add-in does...it takes data that has been dumped into an Excel file from Impromtu and re-arranges it and formats and makes it look pretty and presentable to be manipulated by our company's contracts managers who then need to distribute this version to many different people in the company. At that point the add-in is supposed to intercept the print command and execute my code to email and generate logs and such. Worked fine until this latest batch of computers. Oh well, since the first event fires just fine I just replaced the code behind the print buttons during the startup event (in the add-in) and then that same add-in resets the print buttons when they open excel again and the critia is not true for this certain workbook type.

Thanks for the suggestions, in restructuring the add-in I actually have cleaner execution on our older computers too.

Charles

 

[combo]

Charles, thanks for the explanation. Anyway, I would check, computer by computer, if in excel dialog about add-ins your add-in is visible and checked.
Secondly as you told, the add-in is shared in net. I have no experience with such case, but I wonder what happens if a second computer opens this file, does Workbook_Open event still fires (it is crucial here)? I would add a small message to check it. If not, may be a local add-in should be added to install your net add-in?
Just some Saturday ideas, good luck!