Automatic Sort When Formula Updates

[spider227]

Hey, I'm both new to this board and VBA... and I'm hoping someone can help me with this VBA question.

I'm trying to have a sheet sorted automatically when any cells in a column are changed due to a formula. All I know right now is how to make the sort happen when I use a hotkey to trigger a macro.

From my limited understanding, I think I need to use something like Worksheet_Calculate() rather than Worksheet_Change(ByVal Target As Range).

Following is what I have so far, which obviously doesn't do the job. Could someone help by showing what it needs to look like to work?

Thanks very kindly,
S

Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Application.Intersect(Worksheets("Dashboard").Range("O:O"), Target) Is Nothing) Then
DoSort
End If
End Sub

Private Sub DoSort()
Worksheets("Dashboard").Range("2:1147").Sort Key1:=Worksheets("Dashboard").Range("q2:q1147"), Order1:=xlDescending
Worksheets("Dashboard").Range("2:1147").Sort Key1:=Worksheets("Dashboard").Range("j2:j1147"), Order1:=xlDescending
Worksheets("Dashboard").Range("2:1147").Sort Key1:=Worksheets("Dashboard").Range("o21147"), Order1:=xlDescending
End Sub

 

[Sandeep Warrier]

Hi & Welcome to the Board!

Have you tried using the Worksheet_Calculate() event?
<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden"><input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

 

[spider227]

Thanks for your response! I'm not very familiar with VBA, so I've tried to use Worksheet_Calculate(), but I really have no idea how to use it properly.

Would you be able to give an example of it using the data in the original post?

Thanks,
S