Need macro help restricting when it is executed

[graduate106]

Hi i have an excel workbook whereby when any cell in any worksheet is changed then it executes a module which works really well. The problem is it is really slow so i am looking at restricting the execution of this module so that it only executes when specific cells are changed. Can this be done?

I currently have the following

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Source As Range)
' runs when a sheet is changed
Sheetchanges.ChangeSheets
End Sub

And my module is called "sheetchanges" which has all of the macros in.

Do i need something like this in there somehwere?

If Intersect(Target, Range("A1,B1")) Is Nothing Then Exit Sub

I'm a bit of a newby with marcos to be honest and i got help writing the above macro.

Any help would be much appreciated

Thanks

[JasperD]

Try the following - change Range("A1:A10") to whichever range you want to check.

Please Login or Register  to view this content.

[graduate106]

Thanks for the speedy response!

What i want it to execute when i change say cells A1,B1 and C1 on sheet 1, and then cells A2,B2 and C2 on sheet 2 and then maybe a1:a20 on sheet 3?

[JasperD]

You need to make a Worksheet_Change macro on each sheet, with the range on _that sheet_ in it.
You can't use "Worksheet_Change" with a range on another worksheet, sorry.

[graduate106]

You need to make a Worksheet_Change macro on each sheet, with the range on _that sheet_ in it.
You can't use "Worksheet_Change" with a range on another worksheet, sorry.

To save putting the same macros in every single worksheet though, i put them all in a module, and then fired the module by putting the workbook_sheetchange macro in the workbook section.

Is there no way something can be written into the workbook macro rather than to put a set of macros on every worksheet (of which they are about 30)

Thanks

[JasperD]

You have to put a Worksheet_Change on each worksheet, but you can refer / fire the macro's from your module from there. That's the easiest way.

[graduate106]

Many thanks, this does appear to be doing what i want it to do!

One problem though, some of my sheets already have macros on and i dont know how to get it to deal with 2 different macros.

For example one sheet has the following on it, how and where would I slot in the macro above that you mentioned?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
If Target.Address(False, False) = "D10" Then
For i = 1 To Target.Value
Sheets(i + 4).Visible = True
Sheets(i + 4).Name = Range("D" & i + 11).Value
Next i
If i = 20 Then Exit Sub
For i = Target.Value + 1 To 20
Sheets(i + 4).Visible = False
Next i
ElseIf Not Intersect(Target, Range("D12:D31")) Is Nothing Then
Sheets(Target.Row - 7).Name = Target.Value
End If
End Sub

[JasperD]

Slot it in in the beginning, but as follows :

Please Login or Register  to view this content.

[graduate106]

PERFECT! Works a treat, thank you for all of your help - very much appreciated!

[graduate106]

Sorry to hassle again, but to go one step further, is it possible to get a macro to fire when the value of a particular cell changes? The cell in question is a calculated figure so i will never be actually changing that cell

If i change another cell which in turn changes the calculated cell then the above macro does not fire.

Easy example A20=sum(A1:A19) i want a series of macros to fire on sheet2 when a20 changes (which could be because of any change i make to A1:A19).

Hope that makes sense (and if so, is actually possible!)

Thanks again

[JasperD]

Why then, check for changes in A20 instead of changes in Range("A1:A19") ?

Put the range so you check the cells that are actually (manually) changed, instead of a calculated result and you should be set.
If *all* the cells you want to check are calculated results and none are manually changed, it's a bit more of a challenge, but then you could solve it by using a helper cell.
For example, you want a macro to fire when A20 changes, but A20 is calculated from cell changes on another sheet - how to go about it?
I'd do something like this :

Please Login or Register  to view this content.

How does that work?
If cell Z1000 is empty, then the value in cell A20 is put in it ; From that moment on, cell Z1000 has the same value as cell A20, which is checked on _every_ change of the worksheet.
If cell A20 value somehow changes, then A20 value is different from Z1000 value and your code will run.

Hope this makes sense.

[graduate106]

Clever stuff this! Bit beyond me but I'll be impressed if i can get it working!

Right then that code above seems to work when cells are changed on the same sheet as A20 and Z1000 but A20 might change due to a cell being changed on another sheet and then i might not change another cell on the same sheet as A20 so the check does not run and therefore my code does not run.

My problem is (and to give you a feel of what i am trying to achieve), i enter data on sheets 1,2,3 and 4 which then pull through onto sheet 5 and amount to a total for each person. So on sheet 5 i have say 20 columns for 20 different people which pulls data from sheets 1-4.

When the total at the bottom of each persons column is changed i want it to fire the macro for that persons sheet (after sheet 5 i have another 20 sheets, 1 for each person).

If you could revise the above code to work for this scenario then i will be forever grateful

Many many thanks for your help on this...

[JasperD]

In that case, put a worksheet_change event on sheets 1 / 2 / 3 / 4 that checks the final value on sheet5

For example, you could put this on sheet1 :

Please Login or Register  to view this content.

Basically it checks on any change on the sheet if the final value on sheet 5 has changed - if so, it will fire the macro that you assign to it, if not, it'll ignore and continue
Should work, no?

[graduate106]

Fantastic this is working a treat now!

At the minute where you have put "your code here or call the macro...", i have just put the actual code and scrapped my module.

How would i get it to fire the code on say sheet 6 instead of having the code here? Then it would be just about perfect!!

Many thanks, much aprpeciated

[JasperD]

Just put that code in a macro that you call "mymacro" in a module and put the name of the macro on the part "your code here" :

Please Login or Register  to view this content.

[graduate106]

The macros are already on the sheets i copy into the workbook so that last question was to stop me having to copy the out of the worksheet onto the other ones, so copying them into a module will not save me any further time.

Thanks very much for all of your help though - my spreadsheet is working perfectly now!