Previous Topic Next Topic

Excel worksheet event to calculate cells

Closed
Fleetwd - Jan 7, 2010 at 07:58 AM
 fleeter - Jan 8, 2010 at 01:43 PM
Hello,

My worksheet has two columns that need qualitative values chosen from drop downs that users select depending on risk severity and likelyhood. I have a working macro in a standard module that works when manually run. The macro gives a value to the user choices where there is data then calculates overall risk score in a third column. I am trying to get the macro to automatically run when data is in both columns by using either a calcualte event or change event in the worksheet. I used both a calculate and a change event on different worksheet samples and neither are working to automatically call the module.
----------------
Private Sub Worksheet_Calculate()
Dim ScoreRange As Range
Set ScoreRange = Range("I2:J60")
If ScoreRange Is Nothing Then
Exit Sub
Else: Call CalRiskScore

End If

End Sub

--------------

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address = "$I2:$J60" Then

Call CalRiskScore
End If


End Sub

2 replies

Answer 1 / 2
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Jan 7, 2010 at 08:33 PM
In the absence of the data it is difficult to say. however try this modified event code and confirm whether you succeed . 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'If Target.Address = "$I2:$J60" Then
If Target.Column <> Range("I1").Column And Target.Column <> Range("J1").Column Then
Exit Sub
ElseIf Target.Row = 1 Or Target.Row > 160 Then
Exit Sub
Else
 Application.Run "CalRiskScore"
End If


End Sub
1
Answer 2 / 2
fleeter
Jan 8, 2010 at 01:43 PM
THanks for the reply venkat926, however the SelectChange event code did not work. There were no errors. The sub did not automate the running of the macro.

The data "entered" into Columns I and J are text choices [verylow,low,medium,high,veryhigh]. The macro that I want to call from the event sub is in a standard module. The macro runs through a 5X5 matrix comparing results entered , assigning each I and each J qualitative combination a decimal value, multiplies them together and places the resulting percentage into column K.

Perhaps I need to use the Calculate private sub event for this to work?
0