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?

 

[SkipVought]

Hi,

It all depends on how your logic is arranged.

I'd put a break EARLY in the Worksheet_Change event and step thru the process, using the Watch Window to observe VALUES.

"Pick List" -- what kind of control?

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[RivetHed]

Hi Skip,

I have been stepping through it and the values are correct i.e. the GetDataCol function is showing the value I'd expect it to show but stepping to the next line does not return to the calling procedure rather to the End Function line of a completely different function. It doesn't pass through any of that function to get there, it just skips straight to the last line.

There are no labels there and I've commented out the error handling for testing so it's not doing a goto or anything.

Picklist = Excel data validation list.

 

[Chance1234]

I am wondering if it is having a focus problem from the control back to the sheet.

What i would do is put a break at the start of the function, click on the worksheet you are referencing , then resume the code and see what happens.

Chance,

F, G + 1MSTG

 

[RivetHed]

I wouldn't have thought so since it's not an actual combobox rather an inbuilt extension of the cell.

Tried it anyway and it won't let me click anywhere mid-execution.

Strangely it behaves this way as well when values are pasted into a cell rather than typed.

The other function that it's mistakenly ending up at is used in the worksheet, I don't know if that has any relevance since calculation's switched to manual.

 

[Chance1234]

do you have application volatile in any of your functions ?

ps might be some useful info here #

http://www.decisionmodels.com/calcsecretsj.htm

Chance,

F, G + 1MSTG

 

[RivetHed]

Nope nothing volatile. Also since calculation has been set to manual no UDFs should be being called from the worksheet.

 

[Chance1234]

can you post your full routine up ?

Chance,

F, G + 1MSTG

 

[RivetHed]

Hi chance,

Private Sub Worksheet_Change(ByVal Target As Range)

Dim strTelChange As String

'On Error Resume Next

If Worksheets("Menu").Range("Version") = "Master" Then
    Exit Sub
End If

'If on Soha domain will update Last Change column
'when not on Soha domain will update Contractor Change Date column

If Sheets("Menu").Range("Version") Like "*Soha*" Then
    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
Else
    If Not Cells(3, Target.Column) Like "Contractor Change Date" Then
        Application.EnableEvents = False
        Cells(Target.Row, GetDataCol("Contractor Change Date")) = Date
        Application.EnableEvents = True
    End If
End If

'If phone number amended then add change to log

If Sheets("Menu").Range("Version") Like "*Soha*" Then
    If Cells(3, Target.Column) Like "Tenant Tel No" Then
        strTelChange = "Update " & Cells(Target.Row, GetDataCol("Address 1")) _
                       & " Tel number to " & Target
        Open "H:\Forms\Shared Spreadsheets\ChangeLog.txt" For Append As #1
            Print #1, strTelChange
        Close #1
    End If
End If
     
'If job required then add change to log

If Cells(3, Target.Column) Like "Repair needed" And Target.Text Like "yes" Then
   strTelChange = "Log job for " & Cells(Target.Row, GetDataCol("Address 1"))
    Open "H:\Forms\Shared Spreadsheets\ChangeLog.txt" For Append As #1
        Print #1, strTelChange
    Close #1
End If

End Sub

That's the full worksheet change event. Haven't had any problems with the rest of it it's just that first section that I posted up earlier which calls the above function.

I think this has to be an Excel bug of some description as it does work exactly as intended when you type a value into a cell and I can't see why a different method of the value arriving in the cell should cause this bizarre behaviour.

 

[xlbo]

What is the function that it skips to instead of ending GetDataCol ?

is GetDataCol used anywhere on the worksheet itself?

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

 

[Chance1234]

do "Last Change" and "Contractor Change Date" change positions ? what was the logic behind searching for the column headings ?

A trick which i think i got from skip, is using named ranges and offsetting.

I have a spreadsheet

which has stock data , date, Open , High , Low , Close starting from A2 on a sheet.

I have used the following to define the range

=OFFSET(OHLC!$A$2,0,0,COUNTA(OHLC!$A$2:$A$64011),1)

This will always select the entire column. I can then update the new daily entries via the following

With Range("ohlc_tsco")

    .Offset(Range("ohlc_tsco").Rows.Count, 0).Resize(1, 1) = Date
    .Offset(Range("ohlc_tsco").Rows.Count - 1, 1).Resize(1, 1) = Range("tsco_O")
    .Offset(Range("ohlc_tsco").Rows.Count - 1, 2).Resize(1, 1) = Range("tsco_H")
    .Offset(Range("ohlc_tsco").Rows.Count - 1, 3).Resize(1, 1) = Range("tsco_L")
    .Offset(Range("ohlc_tsco").Rows.Count - 1, 4).Resize(1, 1) = Range("s_tesco")
End With

This will always add a new line to the end of the table.



Chance,

F, G + 1MSTG

 

[RivetHed]

Hi Geoff,

GetDataCol is not used at all on the worksheet. The function it skips to is this:

Public Function XLDateAdd(Interval As String, Number As Double, InputDate As Date)

'Add VBA DateAdd function to workbook

XLDateAdd = DateAdd(Interval, Number, InputDate)

End Function

Which is used in the worksheet, however it skips straight to the end function line rather than the start and stepping into from there just causes the VBA window to flicker for a while and then stop running.

 

[RivetHed]

Chance,

The logic behind searching for column headers was leaving behind a spreadsheet which my boss could amend after I'm gone without having to muck about with any code and since he was insistent on using Excel for this project despite advice against it there's quite a lot of code.

All range names I'm using are dynamic already so he can add rows and columns etc and all the various subs in the workbook should still run ok.

 

[Chance1234]

If there are dynamic ranges in place, why not use these instead of the .Match(strFind)/?

Chance,

F, G + 1MSTG

 

[RivetHed]

Hmm. Just tried removing all reference to the function call and have stripped the offending line down to this:

Cells(Target.Row, 8) = "AM"

And it's still doing it.

 

[xlbo]

Does XLDateAdd reference the cell that is being changed? ie is Cells(Target.Row, 8) part of any argument used in XLDateAdd?

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

 

[Chance1234]

goign for the wild now, any merged cells ?

Chance,

F, G + 1MSTG

 

[RivetHed]

Geoff,

No the cell in question isn't referenced by any formulas at all, neither is the cell I'm changing to fire the event and the whole procedure runs perfectly when values are typed into the cell.

 

[RivetHed]

Chance,

Unfortunately yes there are some merged cells but they're all in a row that's not referenced by any formulas or code.

 

[xlbo]

Hmmm - without seeing or tinkering with it, I'd be hard pushed to think of another reason why the code would be jumping to that function....you don't have any volatile functions around do you? Now (in VBA) or =Today() (on the spreadsheet). INDIRECT and OFFSET are volatile as well - they may be causing the code to fire in an unexpected sequence. Would suggest putting a breakpoint on teh 1st line of XLDateAdd, a breakpoint on the 1st line of GetDatacol and 1st line of worksheet change event

Make the change and see what is fired 1st - I have a feeling that XLDateAdd is being fired before worksheet change which is why it looks like the code jumps to the end of XLDateAdd after the other function call

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