Excel worksheet event to calculate cellsClosed

Question

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

venkat1926 · Answer

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

fleeter · Answer

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?

Excel worksheet event to calculate cells

2 responses

Similar discussions

Subscribe To Our Newsletter!