VB event triggered befire built in sorting

[mike_d124]

I want to trigger an event that checks to be sure all used columns are selected before performing a sort. I would hope that there is some sort of built in feature... but maybe not. HELP!

 

[westconn1]

what sort of event? a message box?

if not selection.address = "$A:$F" then msgbox "incorrect columns"

or
if not selection.address = sheets("somesheet").usedrange.address then msgbox "not all selected"

 

[mike_d124]

That looks right, but I want it do that only when I sort data. The event is sorting.

 

[RoryA]

There is no such event, I'm afraid.

 

[mike_d124]

Thanks! Saves me a lot of time searching for what's not there.

 

[Colin Legg]

Presumably what you can do though is handle the various commandbarbutton click events which preempt a sort.

Something like this?

ThisWorkbook Class Module

Option Explicit
 
Private pSortButtons As Collection
 
Private Sub Workbook_Open()
    Dim SortEvent As c_BeforeSort
 
    Set pSortButtons = New Collection
 
    'sort (from Data Menu)
    Set SortEvent = New c_BeforeSort
    Set SortEvent.cbbSort = Application.CommandBars.FindControl _
        (Type:=msoControlButton, ID:=928)
    pSortButtons.Add SortEvent
 
    'sort ascending
    Set SortEvent = New c_BeforeSort
    Set SortEvent.cbbSort = Application.CommandBars.FindControl _
        (Type:=msoControlButton, ID:=210)
    pSortButtons.Add SortEvent
 
    'sort descending
    Set SortEvent = New c_BeforeSort
    Set SortEvent.cbbSort = Application.CommandBars.FindControl _
        (Type:=msoControlButton, ID:=211)
    pSortButtons.Add SortEvent
 
End Sub

c_BeforeSort Custom Class Module

Option Explicit
 
Public WithEvents cbbSort As CommandBarButton
 
Private Sub cbbSort_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
    If ActiveWorkbook Is ThisWorkbook Then
        'let's just select the currentregion.
        If TypeOf Selection Is Range Then Selection.CurrentRegion.Select
    End If
End Su

 

[mikerickson]

One work around might be to have SelectionChange event that will select all of the proper columns.

For example, if your data is in A1:I100 and you want to prevent the user from sorting only A1:H100 (leaving col I as it was), you could use something like this in the Selection Change event.

If Not Application.Intersect(Target, Range("A1:I100") Then
    If Application.Intersect(Target, Range("A1:I100")).Columns.Count > 5 Then
        Range("A1:I100").Select  
    End If
End If

 

[RoryA]

The only issue I can see with Colin's code would be that if you selected say B1:A9 (because you want to sort on column B) and clicked on one of the auto sort buttons, you would actually sort on A instead.

 

[Colin Legg]

Good spot, Rory. I think something along the lines of this would fix that scenario:

Option Explicit
 
Public WithEvents cbbSort As CommandBarButton
 
Private Sub cbbSort_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
 
    Dim rngActiveCell As Range
 
    If ActiveWorkbook Is ThisWorkbook Then
        If TypeOf Selection Is Range Then
            Set rngActiveCell = ActiveCell
            Selection.CurrentRegion.Select
            Intersect(rngActiveCell.EntireColumn, Selection.Cells(1).EntireRow).Activate
        End If
    End If
End Sub