Excel sheet activate

[mellenec]

How can the Sub Sheet_Change code keep running, if I activate a workbook different from
the one the sheet of which is run?
Currently, when I have the focus on the other workbook, the code in the sheet is stopped because the sheet is no longer activated.
I wonder how Excel manages that, because when you put formulas directly in the cells, they are normaly run while other books can be used .

[GeorgeUK]

Not totally sure i follow this, but here goes.

If BA is updating Workbook1 Sheet1
then the worksheet_change event should run on this sheet each time anything in this sheet gets changed.

The only time code won't run is when you have already "broken" the code from running, possibly a break point or debug error.

Have you use the term activesheet within the event so it is then looking at the wrong sheet if you open another sheet or
workbook?

[Ian]

You need to make sure that the coding in your event specifically points to the workbook if you are going to open another workbook at the same time.

eg using the With Statement

With Workbooks("Betfair Assistant").Sheets("Win Market")

End With

[mellenec]

Not totally sure i follow this, but here goes.

If BA is updating Workbook1 Sheet1
then the worksheet_change event should run on this sheet each time anything in this sheet gets changed.

The only time code won't run is when you have already "broken" the code from running, possibly a break point or debug error.

Have you use the term activesheet within the event so it is then looking at the wrong sheet if you open another sheet or
workbook?

I have added
Workbooks("Workbook1).Activate starting the event,
so the code can now run undisturbed with the right workbook, but when I go to another Workbook, I lose the focus from it as soon as a change comes up in Workbook1 sheet.....

Using the With ... EndWith statement suggested by Ian is fruitless because it's just a coding facility

[GeorgeUK]

Sorry - we'd need to see some of the code to see what is happening.

It shouldn't matter if it is the active workbook. The code will run when that particular sheet changes, so there must be something in the code altering this. It may just be a few lines defining where the data is to go.

Instead of With Workbooks("Betfair Assistant").Sheets("Win Market")
maybe try set Workbooks...

[Ian]

The activate statement wouldn't work because as soon as the event is triggered the focus would switch away from your other wookbook. I thnk George was asking whether you had an activate statement rather than to add one. You need to post some of the code so we cann look at it.

[mellenec]

The activate statement wouldn't work because as soon as the event is triggered the focus would switch away from your other wookbook. I thnk George was asking whether you had an activate statement rather than to add one. You need to post some of the code so we cann look at it.

below part of the code:

Private Sub Worksheet_Change(ByVal Target As Range)
Static raceName As String
Static nbFois As Integer
Static nbChev As Integer
Static nbChevLast As Integer
Static IsPret As Boolean
Static valPermanenteCopiee As Boolean
Const PremRowChev = 4

Workbooks("CurrentTestLatest").Activate ' the added line to avoid
Set F1 = Sheets("Feuil1") ' "index doesn't belong to selection" message here
Set F2 = Sheets("Feuil2")

If F1.Range("A1").Value <> raceName Then
nbFois = 0
IsPret = False
raceName = F1.Range("A1").Value
valPermanenteCopiee = False

F2.Range("A1:G30").Clear
' etc.....

End If

'...................

End Sub

[Ian]

Get rid of the activate statement and try


Set F1 = Workbooks("CurrentTestLatest").Sheets("Feuil1") .....etc.


I prefer to use With

eg With Workbooks("CurrentTestLatest").Sheets("Feuil1")

If .Range("A1").Value <> raceName Then

etc etc

End With

[mellenec]

Get rid of the activate statement and try


Set F1 = Workbooks("CurrentTestLatest").Sheets("Feuil1") .....etc.


I prefer to use With

eg With Workbooks("CurrentTestLatest").Sheets("Feuil1")

If .Range("A1").Value <> raceName Then

etc etc

End With

Well done Ian ! It works now.

What was actually missing was the Workbook reference.


Thank you to both of you, Ian & George.

[Ian]

Glad to help.

I knew all along what was wrong - just wanted to see if you had a winning strategy but you didn't give much away.