Excel Worksheet Change Event Problem 1

[RivetHed]

Hi folks,

Having a really weird problem with a worksheet change event. I'm supposed to have the spreadsheet log which user last amended a row, easy enough, but it's not behaving itself. This is the bit that's causing the problem:

    If Not Cells(3, Target.Column) Like "*Last Change*" Then
        Application.EnableEvents = False
        Application.Calculation = xlCalculationManual
        Cells(Target.Row, GetDataCol("Last Change")) = UCase(Left(Environ$("Username"), 2))
        Application.Calculation = xlCalculationAutomatic
        Application.EnableEvents = True
    End If

GetDataCol is this UDF

Public Function GetDataCol(strFind As String) As Integer

GetDataCol = Application.WorksheetFunction.Match(strFind, Worksheets("Data").Range("3:3"), 0)

End Function

This runs fine when you type a new value into a cell however when you change the cell value with a picklist when it gets to the End Function line rather than going back to the calling procedure it skips to the End Function line of a completely different function. Stepping past this line, or even stopping code execution at that point, causes the VBA window to flicker for a few seconds and then it stops having not made the change.

I've switched off the error handling and it's not throwing out any errors. I've tried recompiling and it still has the same problem.

Is there any reason why selecting data with a picklist should cause this behaviour when typing a value doesn't?

 

[Chance1234]

If you take out your error handling no the worksheet event

do you get an error message at this poitn in the function ?

Application.WorksheetFunction.Match(strFind, Worksheets("Data").Range("3:3"), 0)

Chance,

F, G + 1MSTG

 

[Chance1234]

SOrry also

Cells(Target.Row, GetDataCol("Last Change")) = UCase(Left(Environ$("Username"), 2))

shouldnt that be

Sheets("Menu").Cells(Target.Row, GetDataCol("Last Change")) = UCase(Left(Environ$("Username"), 2))

otherwise you are changing the cell on teh sheet where you have the event running which would cause an infinite loop

Chance,

F, G + 1MSTG

 

[RivetHed]

Aha, you're right it is firing to the XLDateAdd function first. Yes I do have Indirect and Offset being used in the workbook and thinking about it some of those indirects will be referencing the cells in question.

Any ideas on why this is cauing a problem only in certain circumstances? I've just checked and the firing sequence is the same no matter which method the data enters the cell but it only causes problems when data is selected from the validation list or pasted.

 

[RivetHed]

Chance,

I've temporarily removed the call to the GetDataCol function so that's not the issue. All error handling has been removed while testing and no errors have been thrown out.

And yes I am changing a cell on the same worksheet the change event is firing from, that's why it disables events before making the change.

 

[Chance1234]

Have you tried changing your on error resume next to a proper error handler ?

might be something missing from using the resume next.

Chance,

F, G + 1MSTG

 

[RivetHed]

Chance, it's commented out and still not producing any errors.

 

[xlbo]

hmmm - couldn;t tell you exactly why it is firing like that but chances are that it is causing an issue due to the way calculations are being run. You have:

Application.EnableEvents = False
Application.Calculation = xlCalculationManual

before you make a change (nothing wrong with that btw) but that may interfere with excel's normal calculation precedents.

This might be worth a shot in the dark:

As the 1st line of your udfs enter

Application.volatile = false

This should stop them recalcing when the sheet recalcs..

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[RivetHed]

Geoff, it worked!

Have a very well earned star.

Chance, thanks for your time as well.

 

[xlbo]

Happy to help

Only issue with that is you need to be careful about when the udfs DO calculate. You should be ok as I believe that they should still recalc when any of the input parameters are changed - they just shouldn't recalc when the worksheet calcs - might be worth a quick test though



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[RivetHed]

Help files would indicate that they should recalc whenever the input changes. Should be relatively simple to force calculation in any case.