delaying Worksheet_Calculate event or write data in before it gets sorted

[tomanton]

Hi excel gurus. I've got a user form which collects some metadata. For users convenience this metadata gets sorted in a s/s by two columns J & L with a worksheet calculate event. the problem occurs with data that is beyond sorted column J, so whatever gets written in columns further to it doesn't get sorted in line with the rest data. My question would be is there a way to write data from user form before worksheet calculate event kicks in? or any other workaround. I'll try removing worksheet calculate event and add sorting in a sub which will be called after data gets written in. will update on the outcome.

 

[Andrew Poulsom]

Can't you expand the sort range to include the other columns?

 

[tomanton]

Sorted! it worked with calling a sub at the end of writing data in, instead of having worksheet calculate event

 

[tomanton]

well to my surprise at the beginning no! but then I added msgboxes to see when worksheet calculate event gets fired. So when I submit data on userform it gets written cell by cell and worksheet calculate even is called after each cell! I thought at first worksheet calculate event would start after everything gets written

 

[Andrew Poulsom]

You can suppress the event with:

Application.EnableEvents = False

Set it back to true when you want to re-enable the event.

 

[tomanton]

well I did, that's why I had but it didn't work

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
MsgBox "starts here", vbOKCancel
'original data range
'Range("b3:l23").Sort Key1:=Range("J3:J23"), Order1:=xlAscending, Header:=xlYes, _
'new extented range
Range("b3:P23").Sort Key1:=Range("J3:J23"), Order1:=xlAscending, Header:=xlYes, _
Key2:=Range("L3:L23"), Order2:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
          Orientation:=xlTopToBottom
Application.EnableEvents = True
MsgBox "finishes here", vbOKCancel
End Sub

 

[Andrew Poulsom]

No, in your UserForm code that updates your data.

 

[tomanton]

yeah, that's a point. No I fully understand what enableevents means. thanks Andrew!