About the event sheet

[London_Calling]

In the context of Worksheet_Calculate and using Application.EnableEvents = False / True, it's driving me a little bonkers that I can't seem to find a solution to vba returning to the top of the event sheet every time it's asked to write something to that sheet (like "BACK" or "LAY").

I would just like it to continue through the code in a normal, linear fashion.

Am I missing something or are there tips and tricks I'm not aware of because it really is a bit frustrating . . .

Cheers

*breaks a nail*

[osknows]

Hello,

If you can post up your code we could take a look at what might be causing it

Regards,
Os

[London_Calling]

Hi osknows, as far as I understand, what I described above is the way vba is supposed to work e.g. if you use Worksheet_Change and write to the sheet, it will revert back to the start.

Assuming that is correct, I'm asking if anyone knows or uses work arounds.

Cheers.

[osknows]

Generally yes, by writing to a sheet you trigger either a Worksheet_Change event and/or a Worksheet_Calculate event unless you have turned off events using Application.Enableevents = False.

If you're calling many procedures then events may be being enabled elsewhere in the code.

eg the code below will trigger an event after writing range("a1").value = 40

Code: Select all

Sub Test1()
Application.Enableevents = False

range("a1").value = 10 'this will not trigger an event
Test2  ' call sub Test2

range("a1").value = 40 'this will trigger a worksheet change event

Application.Enableevents = True
end Sub


'=============

Sub Test2()
Application.Enableevents = False

range("a1").value = 20 'this will not trigger an event

range("a1").value = 30 'this will not trigger an event

Application.Enableevents = True
end Sub


[Royville]

Hi London Calling & OS,

Hope you don't mind my crashing in like this, I don't mean to take over the thread or anything but I also am having trouble getting my head around these event sheets. Perhaps airing/solving my problem may be of help to you also.

I have added a fourth sheet and want to log data there from sheet1. (Its just writing a line of odds data every minute or so).
I put the code into sheet4 Worksheet_Change event sheet and it just would not run.
So I put the code into sheet4 Worksheet_Calculate event sheet and it ran, but it was writing multiple lines of the same data when it should be writing only one.
So I put the code into a module and called it from sheet 1. This seemed to work ok but when I went from viewing sheet 4 in excel to any other sheet it stopped and came up with 'object errors'.
So my question is . . .
How do you set up the code to write to sheet 4 - even if sheet 1 (or any other sheet) is selected in excel itself?

Please forgive my intrusion
Regards
Roy.

[greenfingers]

I put the code into sheet4 Worksheet_Change event sheet and it just would not run.

I was also driven mad by this when I wrote my first sheet. In my case it was because of the Application.EnableEvents being set to FALSE at the start of the code and then an error in my code causing the procedure to abort before it got to the end wherre it is reset to TRUE. You can check the state of this by typing Print Application.EnableEvents in the Immediate window when your code isn't running. If it's FALSE you can then change it by typing Application.EnableEvents = TRUE in the immediate window. Your code should then run when you re-attach the sheet to BA. Hope this helps.

[greenfingers]

In the context of Worksheet_Calculate and using Application.EnableEvents = False / True, it's driving me a little bonkers that I can't seem to find a solution to vba returning to the top of the event sheet every time it's asked to write something to that sheet (like "BACK" or "LAY").

I would just like it to continue through the code in a normal, linear fashion.

Am I missing something or are there tips and tricks I'm not aware of because it really is a bit frustrating . . .

Cheers

*breaks a nail*

I may be off the mark, but do you have an error handler? If you didn't and there was an error in your code that writes the text to the sheet, then it would abort the code and return to the top on the next worksheet calculation????

[osknows]

Hello Roy,

Generally in most VBA code all ACTIVATE and SELECT statements should be removed as there is no need to select or activate anything to read or write to cells.

The example below shows how to copy a range from Sheet1 to Sheet4 using the change event in sheet1 via loading a range into an array and then pasting the array to a new sheet. The advantage of this is that any required calculations can be performed in the array before pasting back out to an excel sheet. It's much quicker as the read and write process happens only once.

Put this in the SHEET1 object

Code: Select all

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim BAarray() As Variant
Application.EnableEvents = False


With ThisWorkbook.Sheets(1)
'code to do what you need in Sheet 1
BAarray = .Range("a1:e5").Value
End With




With ThisWorkbook.Sheets(4)
'code to do what you need in Sheet 4
.Range(.Range("A65536").End(xlUp).Offset(1, 0), _
.Range("A65536").End(xlUp).Offset(UBound(BAarray, 1), UBound(BAarray, 2) - 1)).Value = BAarray
End With

Application.EnableEvents = True
End Sub


When a cell(s) changes on Sheet1, the range a1:e5 is copied into an array named BAarray

The range is then copied to Sheet4 by
1. Finding last used cell in column A
2. Offsetting by 1 row to find next empty cell in column A
3. Re-dimensions the range to write to based on the array size
4. Writes the array to Sheet4 based on the range size in 3.

[osknows]

I may be off the mark, but do you have an error handler? If you didn't and there was an error in your code that writes the text to the sheet, then it would abort the code and return to the top on the next worksheet calculation????

Error handling is normally good practice. Below is an example I use for more complex projects which turns back on events, screenupdating etc before reporting an error.

Again in the SHEET1 object

Code: Select all

Option Explicit
Dim screenUpdateState, statusBarState, calcState, eventsState, displayPageBreakState As Boolean

Private Sub turn_off_events()
        screenUpdateState = Application.ScreenUpdating
        statusBarState = Application.DisplayStatusBar
        calcState = Application.Calculation
        eventsState = Application.EnableEvents
        displayPageBreakState = ActiveSheet.DisplayPageBreaks 'note this is a sheet-level setting
        Application.ScreenUpdating = False
        Application.DisplayStatusBar = False
        Application.Calculation = xlCalculationManual
        Application.EnableEvents = False
        ActiveSheet.DisplayPageBreaks = False 'note this is a sheet-level setting
End Sub

Private Sub turn_on_events()
        Application.ScreenUpdating = screenUpdateState
        Application.DisplayStatusBar = statusBarState
        Application.Calculation = calcState
        Application.EnableEvents = eventsState
        ActiveSheet.DisplayPageBreaks = displayPageBreakState 'note this is a sheet-level setting
End Sub



Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo PROC_ERR
Dim BAarray() As Variant
turn_off_events


With ThisWorkbook.Sheets(1)
'code to do what you need in Sheet 1
BAarray = .Range("a1:e1").Value
End With


With ThisWorkbook.Sheets(4)
'code to do what you need in Sheet 4
.Range(.Range("A65536").End(xlUp).Offset(1, 0), _
.Range("A65536").End(xlUp).Offset(UBound(BAarray, 1), UBound(BAarray, 2) - 1)).Value = BAarray
End With
turn_on_events
Exit Sub

PROC_ERR:
    turn_on_events
  MsgBox "Error: (" & Err.Number & ") " & Err.Description, vbCritical
End Sub



[osknows]

Sorry, I forgot to mention the following;

Events are things which trigger after something happens so will usually determine where to place code.

Eg. If you want something to happen after updates to a sheet then the event to make it run would usually be found in the relevant sheet object.

If you want something to happen after updates to the workbook then the event to make it run would usually be found in the ThisWorkbook object.

If you want something to happen based on time intervals or code to run independently then it usually would go in a module.

These are just general guidelines and there are many good reasons not to follow them.

As you are trying to copy data from Sheet1 I assumed the change event in sheet1 is the best place for the code as this will trigger after every BA refresh. Additional code can be added to only write after specified durations if required.

[Royville]

Whoops!
Looks like its been taken over after all! - Really sorry London-Calling, I hope its all relevant to your query.

As for myself, I'd like to thank you all for your replies and coding examples, - but being the simple fellow that I am, I must be honest and say that I didn't understand any of them - honestly!. Try as I may - it still beats me.
Fortunatly, I've managed to get my code to write to sheet4 ok. I found it was writing multiple lines (in the sheet4 Worksheet_Calculate Event Sheet) because the code was running so fast, that it would finish and return to the start and run again and again before the timer which determines when it should write - had changed to the next value (1 second). So I put an IF statement in saying . . .

Code: Select all

IF timer has incremented THEN
     write the data.
END IF


At the moment it all seems to be running as expected, so until the next time I'd just like to say Many Thanks . . .
Regards
Roy.