Crashing due to Event

I've been having an issue with Excel crashing when events occur.  I suspect that the issue relates to the environment where the code sites (Hyperion Retrieve running Excel 2000 on a Citrix server) more than any flaw in the code (a fact backed up by the fact I can't replicate the problem on my desktop), but I was hoping someone could take a look at my code and let me know if there was anything obviously wrong as I am relatively new to VBA.  Also, there doesn't seem to be one specific part of the code that causes the problem, but rather the crash will occur the first time through or the fiftieth on any given event occurrence.

Thanks for your help.

Jake

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo Reset:

If Not Application.Intersect(Target, Me.Range("c12:e14")) Is Nothing Then

Application.EnableEvents = False

'Clear Node and Department ranges when not needed
    If Target.Value = "Segment" Then Range("c24:e26").ClearContents
    If Target.Value = "Node" Then Range("C26:e26").ClearContents

'Notify user that Local Currency is only available at the department level
    If Target.Value = "Local" And Range("C12").Value <> "Department" Then
        LocalCurrencyWarning.Show
    End If
    If Target.Value = "Segment" And Range("C14").Value = "Local" Then
        LocalCurrencyWarning.Show
    End If
    If Target.Value = "Node" And Range("C14").Value = "Local" Then
        LocalCurrencyWarning.Show
    End If

'Notify user than the Global view is not available at the department level
    If Target.Value = "Department" And Range("C18").Value = "Global" Then
        GlobalViewWarning.Show
    End If

Else

End If

Reset:
    Application.EnableEvents = True
   
End Sub

Jake

The code per se looks fine. I can see a few places where I would make some changes, but it should not cause a crash. i would suspect the userforms to be the issue as unless you specifically release the memory, you will get all kinds of problems (IMO) eventually.

Make sure when you display the UserForm you explicitly in the Form code use something like

Unload Me

Obviously if you are collecting input from the form you will need to do this before unloading. If you are just using the Userform to advise a user I would use MsgBox. It's far lighter and you do not need to clean up afterwards.

Other than that I see no reason why it should crash.

Lastly, you may try Rob Bovey's free code cleaner at www.appspro.com

Thanks Nick.  I actually used MsgBox initially but when things started bombing out I thought I'd try Forms because a. I wasn't sure what the relative memory burden of message boxes versus forms was and b. I knew I could unload the forms (which occurs after a Buttton Click in the actual form).  Are there any known stability issues for Excel in a Citrix environment?  I've been playing with the spreadsheet for the last half hour in 2007 on my desktop just trying to cause a crash and I have yet to be able to generate anthing other than expected results.

Jake,

I'm with Nick on the UserForm issue.  I would unload the UserForm immediately after you're done with it.  I know you say you're unloading it after the button click, but be sure you are using Unload, and not just Hide.  You probably are, and I'm just being overly cautious.

However I doubt that's the issue if you were having the same problem with message boxes.

The code is extremely simple, and I can't really see it causing a standard error, much less a crash.  I've successfuly run significantly more complex macros remotely through Citrix in both 97 and 2003, although I've never used Hyperion Retrieve.

The first question I have would be what happens with the macro if Hyperion Retrieve is taken out of the equation?

The second question that occurs to me is whether you have difficulty with any other macros on the remote system, or just this particular one?  I wonder if there is an issue with the install of Excel on the remote system.

David B.

Just so we're on the safe side: have you tried running code cleaner (www.appspro.com)?

Maybe even go as far as putting the code in an entirely fresh workbook and see if that runs OK?

Hi Jake,

I have used Hyperion via Citrix before , and the only issue I ever encountered was down to network delay in responding to the query. In 1 particular location it caused Excel to repeatedly bomb out.

Also look at the type of anti-virus software on the machine where the problem is, as sometimes this can cause issues (it shouldn't I know, but it has been known to happen).

Can you post the code you are using for Hyperion as well please?

Thanks for all the feedback!

To answer all the questions thus far:

David, when not working in Hyperion (which is the only Citrix environment I work in) I can run the code just fine.  After my last posting last night I literally sat down in front of a baseball game with my laptop an tried to replicate the error on my own machine for an hour and was unable to do so.  Other macros will run slower in the Citrix environment, but it seems to be only events that cause the application to completely bomb out.  After thinking about it some last night, it occurred to me that it is likely a memory issue.  At the same time that the Events code is running, the user is using data validation drop downs to change the selection criteria for Retrieve, which in turn drives updates across hundreds of different accounts, usually for multiple periods.  I no next to nothing about network environemnts, but it doesn't seem unreasonable given our less than stellar operating environment that I'm maxing out the available system resources.

Jan, I haven't tried running code cleaner yet, but I definitely will and I'll post any updates after.

Alan, I'm not sure what you mean by the Hyperion code.  Are you referring to the Retrieve Formulas?  It's 90% HPVAL with a few others mixed in.  If you mean other Macros within my retrieve I have seven, but none of them seem to cause the problems that Events do.  Which leads me to the unfortunate conclusion that I need to turn Worksheet Events off even though they are by far the cleanest way to enforce combo-edit rules.

Thanks again for all the feedback, if nothing else it's nice to new there's nothing obiviously wrong with my code.