Excel worksheet event to calculate cells

[Closed]
Report
-
 fleeter -
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

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
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
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

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?