Target.Columns.count and refreshes

[Fixador]

2 queries :-

(1)I realise to align WITH price updates , i need to use

Code: Select all

If Target.Columns.count <> 16 Then Exit Sub

or

Code: Select all

 If Target.Columns.count = 16 then
  (... my code ..... )
end if

But what if : i had several steps in my calculations - which would be outputted to the sheet - WITHOUT further reference to the price data - where the output of the prior step, is a input to the next step

How could i have excel execute the 7 refreshes - in between the refresh from Target.Columns.count = 16

With my BA refresh rate at 1 sec - i would have to wait 1 second for each step to complete and refresh the sheet.




(2) 2nd query: Can anyone give me a nutshell explaination when to use the following different procedures - what exactly each means !

(a)

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)

and

(b)

Code: Select all

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Hope this makes some kind of sense !

[Ian]

1. You use "Application.EnableEvents = False" to prevent the WorksheetChange event occurring again whilst you are doing your calculations.

2. I think the difference is that one only works when there is a change to the worksheet to which it is attached, the other will work if any worksheet in the workbook is change,

[Fixador]

Thanks Ian with (2)

My Puzzle deepens with (1) however, i am trying to use one cell as a marker for executing concurrent code, but with a refresh between - so....

Code: Select all

'Now outside  Target.Columns.count = 16  - so will this work ?
Application.ScreenUpdating = False
   ThisWorkbook.Worksheets.Application.EnableEvents = False:   
   ThisWorkbook.Worksheets.Application.Calculation = xlCalculationManual

If target.address($AZ$1) then ' could have several subs fired from change here - inbetween price updates

   If $AA$1 ="BBBB" then
   ...my code#2.....
   
   end if
   If $AA$1 ="AAAA" then

    ...my code#1.....
   $AA$1 ="BBBB"
   end if
end if

If Target.Columns.count = 16 Then
   
     '...... code that is dependent new price data
    ' and ends with
    sht.cells(27,1) ="AAAA"

End if
    Application.ScreenUpdating = True
   ThisWorkbook.Worksheets.Application.Calculation = xlCalculationAutomatic:
     ThisWorkbook.Worksheets.Application.EnableEvents = True


Until sht.cells(27,1) ="AAAA" occurs - ie, after a price update - then worksheetSheet_change then target.address($AZ$1) becomes valid - doesn't it ?

so far target address has never come up as AA1