Worksheet_Change event conflict? (Excel 2000)

[ChrisGreaves]

In my Excel 2000 Workbook, the code to change styles works.

When I run the function "LoadCurrencyStyles", the style format strings change to dollars, rupees, pounds etc.
To my mind this proves that my numeric-valued cells, formatted with appropriate styles ("Currency0", "Currency1" etc) can adapt to different locations.
I am happy.

I have a Worksheet_Change event:

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    If (Target.Row = 7) And (Target.Column = 2) Then
        Dim strCurrencySymbol As String
        strCurrencySymbol = Range("Currency").Value
        Call LoadCurrencyStyles(ActiveSheet.Parent, strCurrencySymbol)
    Else
    End If
End Sub

which triggers nicely, thank you.
It invokes the "LoadCurrencyStyles" function, and when I stop at the line "sty.NumberFormat = strStyleNumberFormat", the left-hand side of the assignment shows the old format (say, dollars) and the right-hand side shows the new format (say pounds).
I single-step through the assignment and re-check the two values.
To my horror/shame the left-hand side still shows the old format.
Something inhibits the assignment, but no error is produced in the running of the VBA.

Again, the slave code to change currencies works; I don't believe I am generating a syntactically-faulty format string.
It works perfectly, along with the Worksheet_Change event in four other workbooks.

But not in this one.
What is different?
This workbook has three user-defined functions that deliver values.
If I comment out the three functions, the currency format change is effected - perfectly!
But with the functions activated, they seem to inhibit execution of the assignment in the "LoadCurrencyStyles" function.

Why should a user-defined function cause execution of another piece of code to fail - but only on the assignment?
I would expect the VBA code to run NOT in parallel, that is, one of the two chunks of code should run, and then the other.

The user-defined functions are straightforward - a series of calculations on Double valued parameters producing a Double result.

[HansV]

I can't reproduce your problem in Excel 2007 on Windows 7. Care to attach a workbook that exhibits the problem (in Excel 2000)?

[ChrisGreaves]

Care to attach a workbook that exhibits the problem (in Excel 2000)?

I saw that coming!
Yup.
I'll try.
It's proprietary stuff etc so let me vaniliize it ...

[ChrisGreaves]

I can't reproduce your problem in Excel 2007 on Windows 7. Care to attach a workbook that exhibits the problem (in Excel 2000)?

Hans, Please try this (attached).
But we aren't fooling ourselves, are we? We knew that the process of stripping-it-down would reveal the trigger of the problem - although I confess I don't know the 'why' of the trigger.

The code for style changes can be found in module modStyles; that code can be used as a standalone test of the style-numberFormat string changes.
I have placed a 'MsgBox "LoadCurrencyStyles"' where I would normally put a break-point to verify that I am, indeed, passing through that code.

The code for user-functions has been wrung out to dry in modUserFunctions; and toggling 'Application.Volatile' will toggle the behavioral problem.

Meanwhile back at the ranch ...
The worksheet 'Abacus' has a drop-down list for Location.
Changing the location will always issue a confirmation through the 'currency' cell immediately beneath.

If the whole thing works, (with 'Application.Volatile' disabled) the format strings for the style 'Currency0' will be changed, and the appearance of the remaining cells will be changed.

Thanks for offering to inspect.
I suspect that the problem is directly related to my lack of understanding of the side-effects of 'Application.Volatile' .

[HansV]

I tested your workbook in Excel 2007 SP2; the setting for Application.Volatile in the UDF doesn't make a difference - the Worksheet_Change code performs as intended whether Application.Volatile is True or False.

Perhaps Loungers with earlier versions of Excel can test too, and report their findings.

[ChrisGreaves]

... the setting for Application.Volatile in the UDF doesn't make a difference ...

Hans, my apologies for the late response. The problem was resolved that evening by a process of eradicating the VBA code in small steps.
The client wants to know what the problem was and for the life of me I can't remember!
I suspect it was related to an ill-defined range name, perhaps pointing to the contents of a different workbook.

In my Copious Free Time^(TM) I shall resurrect the offending version and repeat the process of elimination.