calculate a range

[MartinL]

Hi Guys

Can anyone help me solve this

I have a cell "Z12" on sheet1 that will change when an option is made using radio buttons.

What I need to do is recalulate a range "C5:L105" on sheet2 whenever this cell "Z12" changes

I'm guessing its

Private Sub Worksheet_Change(ByVal Target As Range)
End Sub

or

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub

but how do I refer to the cell and then recalc the correct worksheet range?

 

[Jerry Sullivan]

Hi Martin,

If you are changing the value of Cell Z12 based on a Linked Cell in an ActiveX Option Button, that change won't trigger any worksheet events.

You can put the code to recalculate the range in the code associated with the Option Button itself.

Note that there are two kind of Option Buttons:
If you are using ActiveX Objects, use the OptionButton1_Change or OptionButton1_Click Event code.
If you are using Form Objects, add your code to the macro assigned to the Object.

 

[MartinL]

Hi Jerry

Thanks for your time

I must have been on a mind bend as it was not radio buttons but a drop down selection box.

I have however managed to sort it now using a mixure of bits of code that I have used in other sheets, appears to work although I have to calculate the sheet twice once after the other for some reason at one point

 

[Jerry Sullivan]

I have however managed to sort it now using a mixure of bits of code that I have used in other sheets, appears to work although I have to calculate the sheet twice once after the other for some reason at one point

Martin, If you post your code, we can have a look and see why it's calculating twice.

Is your dropdown selection box a data validation list, ActiveX, or Form object?

If you are using a validation list, then you could use Worksheet_Change event to trigger your calculation.

 

[MartinL]

Hi Jerry

This is the code I'm using

Sub CalcSheet()
 
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
 
Range("R12:T12").Calculate
Range("Z12").Calculate
 
Sheets("Prize").Select
ActiveSheet.Unprotect
Range("M1:M2").Calculate
Range("C5:L150").Calculate
 
'for some reason this does not calculate correctly when
'when changing from 4 weeks to 52 weeks
'I am using a validation list of 4 choices: "52 weeks", "26 weeks", "13 weeks", "4 weeks". 
'All combinations appear to work except going from 4 to 52 weeks 
'calculating again appears to solve the issue
Range("C5:L150").Calculate
 
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
    True
 
Sheets("Market").Select
Range("M15:N22").Calculate
 
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
    True
 
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
 
End Sub

Martin

 

[Jerry Sullivan]

Could you explain the reason you are using this Macro?
Do you have Calculate set to Manual for a workbook that takes a long time to calculate all?

Which cell is the has the DataValidation? How does that Cell relate to the Cells that you are calculating?

 

[MartinL]

Yes the workbook is set to Manual calculate as it would take around 13 -15 mins to calculate otherwise. And in this instance it is a very simple formula

The dropdown is in Market!M12
The datavalidation list is in Market!V12:V15
In Market!Z12 I have the formula

=IF(M12=V12,1,IF(M12=V13,2,IF(M12=V14,3,4)))

In Prize!M2 I have the formula

=Market!Z12

Prize2 has 8 tables that get populated dependent on the value of Prize!M2

These values are copied directly from Market! and then have simple formulas run over them.
It is the result of these formulas that are returned back to Market!M15:N22

This all looks very complicated as I write it but in practice it is quite simple.

I will try and isolate the the two tabs to a new sheet and set it up for you to look at by downloading it, but it may crash if the other sheets are not available...

Martin


*** I cannot copy or move a sheet that contains a table - Excell error message

 

[Jerry Sullivan]

A couple of other thoughts....

It's better to directly reference your sheets instead of activating or selecting them.
You shouldn't need to unprotect and reprotect your worksheets just to do the calculation.
No need to change the states of DisplayAlerts or EnableEvents unless you have actions in your code that could trigger an alert or event.

The simplified code could look like this...

Sub CalcSheet()
    Application.ScreenUpdating = False
 
    With ActiveSheet
        Range("R12:T12").Calculate
        Range("Z12").Calculate
    End With
    
    With Sheets("Prize")
        .Range("M1:M2").Calculate
        .Range("C5:L150").Calculate
    End With

    Sheets("Market").Range("M15:N22").Calculate
    
    Application.ScreenUpdating = True
 End Sub

EDIT: Oops- didn't see your reply until I posted this. I'll read your comments and respond.

 

[Jerry Sullivan]

I will try and isolate the the two tabs to a new sheet and set it up for you to look at by downloading it, but it may crash if the other sheets are not available...

No need to do that. I'll mock something up with the description you gave and see if I can find the reason you have been needing to calculate twice.

 

[MartinL]

file
http://www.box.net/shared/k5toske6eh014b592dcs