User defined Search Listbox

[UweNaujak]

This is my situation:

I have this Worksheet

ID	Fname	Lname	City	ZIP	Last Date
1​	Joe	Green	Denver	12345​	1/1/2021​
2​	Mary	Gold	New York	64578​	2/1/2021​
3​	Frank	Black	Chicago	45678​	12/1/2020​

and on a VBA userform this ListBox where a User defines Sort Criteria

ListBox

Lname

City

ZIP

Date

In addition I have a report based on all worksheet data. The report in itself works fine. Now I want to give the user the option to select a sort criteria by selecting a “Sortkey1” from within the listbox.
Note: I made sure that Column Headers and Listbox values both are formatted as Text.

The code I’m using is as follows:

Private Sub ListBoxDonReportsSortField_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim i As Integer, Sortkey1 As String
For i = 0 To ListBoxDonReportsSortField.ListCount - 1
If ListBoxDonReportsSortField.Selected(i) Then
Sortkey1 = Sortkey1 & ListBoxDonReportsSortField.List(i) & vbCrLf
End If
Next i
End Sub

Then, within my Report code: (Active sheet is my worksheet)

Dim rgFind As Range
With Range("A1:F1")
Set rgFind = .Find(What:=Sortkey1, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
End With

Dim lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).row
With ActiveSheet.Sort
.SortFields.Add Key:=Range(Cells(1, rgFind.Column)), Order:=xlAscending
.SetRange Range("A1:F" & lr)
.Header = xlYes
.Apply
End With

Help please, this is what I get: Doesn’t work, getting Error 91, Object Variable or With Block not set

I’m using the same code within other applications and rgFind.column typically gives me the correct column. Messing with this now for 2 days, giving up and ask for your help. What am I missing here?
Thanks for your help, Uwe

 

[GWteB]

Judging by the code and the information you provide I think one of the reasons could be that the Sortkey1 variable is locally declared. The double click event handler of the listbox attempts*** to assign a value to Sortkey1 but when it finishes Sortkey1 isn't alive anymore, meaning out of scope for any other code. A solution would be to declare Sortkey1 globally in a standard module. An even better way would be making your sort code dependent on a Sorktkey like in

Public Sub MySortProc(Byval argSortkey as String)

and by "injecting" the Sortkey in the MySortProc procedure, invoked from the list box double click event handler.

I would also like to note that the event handler code might suggest that the value of Sortkey1 is expanded with each pass of the For-Next loop. This is not the case, so the code could also be written like this (with the same effect):

Sortkey1 = ListBoxDonReportsSortField.List(i) & vbCrLf

Finally, I note that the vbCrLf in the Sortkey variable could let boil things up when an attempt to sort actually takes place.

*** in cases your listbox has a visual capacity (on screen) of 10 items and the entire population consists of only 4 items, there is the possibility that the user will double click upon a non-populated area while nothing is selected yet. Run-time errors are very likely.

 

[UweNaujak]

Thanks for the reply. I don't have access over the weekend and will play with your suggestions on Monday, just wanted you to know I'm not ignoring your help. Uwe

 

[UweNaujak]

Judging by the code and the information you provide I think one of the reasons could be that the Sortkey1 variable is locally declared. The double click event handler of the listbox attempts*** to assign a value to Sortkey1 but when it finishes Sortkey1 isn't alive anymore, meaning out of scope for any other code. A solution would be to declare Sortkey1 globally in a standard module. An even better way would be making your sort code dependent on a Sorktkey like in

Public Sub MySortProc(Byval argSortkey as String)

and by "injecting" the Sortkey in the MySortProc procedure, invoked from the list box double click event handler.

I would also like to note that the event handler code might suggest that the value of Sortkey1 is expanded with each pass of the For-Next loop. This is not the case, so the code could also be written like this (with the same effect):

Sortkey1 = ListBoxDonReportsSortField.List(i) & vbCrLf

Finally, I note that the vbCrLf in the Sortkey variable could let boil things up when an attempt to sort actually takes place.

*** in cases your listbox has a visual capacity (on screen) of 10 items and the entire population consists of only 4 items, there is the possibility that the user will double click upon a non-populated area while nothing is selected yet. Run-time errors are very likely.

I guess my plan may not work.
I'll keep fiddling around with it and in the meantime come up with some alternative, less neat way.
Sortkey1 is declared as a global variable.
Before VBA enters into
Dim rgFind As Range
With Range("A1:F1")
Set rgFind = .Find(What:=Sortkey1, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
End With
the debugger tells me that Sortkey1 still has the value we are looking for with rgFind.
But then somehow it's getting lost.

Thank you for your help anyway, I appreciate it.

 

[GWteB]

You're on the right track but without all the code you have so far, it's difficult to say what's causing your issue.

I guess my plan may not work.

Sure it will work ... I've cobbled together some code and added a few comments. It might give you more insight of a possible approach. First some explanation.

A userform is a special kind of Class, to which we can add methods and properties ourselves. The code below makes use of this possibility. The code behind of the userform only contains the logic of the userform itself and the list box placed on it. The userform's module contains the following:
- a container with local variables (a container so VBE's intellisense is better able to help me ...);
- a double click event handler of the list box;
- a PopulateListBox method (a public Sub);
- a CallBackMacro property (a public Property);
- a RangeToSort property (a public Property).

This goes in the Userform module:

Option Explicit

Private Type SomeLocals
    lbSelected  As String
    CallBack    As String
    RangeToSort As Range
End Type

Private this As SomeLocals


Private Sub ListBoxDonReportsSortField_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Dim i As Long
    
    ' get user's selection
    With Me.ListBoxDonReportsSortField
        For i = 0 To .ListCount - 1
            If .Selected(i) Then
                this.lbSelected = .List(i)
                Exit For
            End If
        Next i
    End With
    
    ' run macro which is responsible for sorting our range
    With this
        Application.Run .CallBack, .RangeToSort, .lbSelected
    End With
End Sub

Public Sub PopulateListBox(ByVal argArray As Variant)
    ' restrict user to one choice a time
    Me.ListBoxDonReportsSortField.MultiSelect = fmMultiSelectSingle
    ' populate list box
    Me.ListBoxDonReportsSortField.List = argArray
End Sub

Public Property Let CallBackMacro(ByVal argCallBackMacro As String)
    ' store macro name
    this.CallBack = argCallBackMacro
End Property

Public Property Set RangeToSort(ByVal argRangeToSort As Range)
    ' store range to sort
    Set this.RangeToSort = argRangeToSort
End Property

Within a standard module there are two separate procedures. One is responsible for:
- define the worksheet range to be sorted;
- retrieve the header texts (to be used later as a search key);
- provide the userform with the necessary data (trough the tailor made methods & properties);
- display the userform on the screen.
The second procedure is responsible for the sort logic. This one is called from the userform at the moment user double clicks on the listbox.

This goes in a standard module:

Public Sub UweNaujak()

    Const COLUMNDELIMITER As String = "*"       ' << this character may not(!!) be used in any header; change to suit

    Dim rng As Range
    Dim arr As Variant
    Dim uf  As UserForm1
    Dim i   As Long

    ' define range to work with
    With ActiveSheet                                                        ' <<< change to suit
        Set rng = .Range("A1:F" & .Cells(.Rows.Count, "A").End(xlUp).Row)   ' <<< change to suit
    End With

    ' obtain its headers and put them finally in an array memory type
    For i = 1 To rng.Columns.Count
        arr = arr & rng.Cells(1, i).Value & COLUMNDELIMITER
    Next i
    arr = VBA.Split(arr, COLUMNDELIMITER)
    
    ' omit last delimiter within array
    ReDim Preserve arr(UBound(arr) - 1)

    ' display userform with list box
    Set uf = New UserForm1

    With uf
        ' your custum method
        .PopulateListBox argArray:=arr
        
        ' custom property #1
        .CallBackMacro = "DoSomeSort"
        
        ' custom property #2
        Set .RangeToSort = rng
        
        ' native userform Show method
        .Show
    End With
End Sub


Public Sub DoSomeSort(ByVal argRange As Range, ByVal argSortKey As String)

    Dim oWs     As Worksheet
    Dim rgFind  As Range
    Dim lr      As Long

    ' determine bottom populated row of range to be sorted, row number related to worksheet
    lr = argRange.Rows(argRange.Rows.Count).Row

    ' search within first row only, in which the headers are
    With argRange.Rows(1)
        Set rgFind = .Find(What:=argSortKey, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
    End With

    If Not rgFind Is Nothing Then
        Set oWs = argRange.Parent
        With oWs.Sort
            ' clear existing Keys
            .SortFields.Clear
            ' add new Key, which is the column to be sorted from top to bottom within range to be sorted
            .SortFields.Add Key:=oWs.Range(oWs.Cells(rgFind.Row, rgFind.Column), oWs.Cells(lr, rgFind.Column)), Order:=xlAscending
            ' range to be sorted
            .SetRange argRange
            .Header = xlYes
            .Apply
        End With
    End If
End Sub

Note that the range which has to be sorted is defined in just one place within the code. Everything else that is needed to ensure that sorting runs smoothly is derived from this. This implies that the code can also be used if the range is elsewhere on the worksheet, instead starting at cell A1. Also note that no global variables are used. Data transfer takes place during the call of a procedure. This allows some errors to be discovered at compile time, rather than at run time. Finally, note that the code assumes a userform named UserForm1, which contains a list box named ListBoxDonReportsSortField.

Hopefully this helps with the further development of your project.

 

[UweNaujak]

You're on the right track but without all the code you have so far, it's difficult to say what's causing your issue.


Sure it will work ... I've cobbled together some code and added a few comments. It might give you more insight of a possible approach. First some explanation.

A userform is a special kind of Class, to which we can add methods and properties ourselves. The code below makes use of this possibility. The code behind of the userform only contains the logic of the userform itself and the list box placed on it. The userform's module contains the following:
- a container with local variables (a container so VBE's intellisense is better able to help me ...);
- a double click event handler of the list box;
- a PopulateListBox method (a public Sub);
- a CallBackMacro property (a public Property);
- a RangeToSort property (a public Property).

This goes in the Userform module:

Option Explicit

Private Type SomeLocals
    lbSelected  As String
    CallBack    As String
    RangeToSort As Range
End Type

Private this As SomeLocals


Private Sub ListBoxDonReportsSortField_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Dim i As Long
   
    ' get user's selection
    With Me.ListBoxDonReportsSortField
        For i = 0 To .ListCount - 1
            If .Selected(i) Then
                this.lbSelected = .List(i)
                Exit For
            End If
        Next i
    End With
   
    ' run macro which is responsible for sorting our range
    With this
        Application.Run .CallBack, .RangeToSort, .lbSelected
    End With
End Sub

Public Sub PopulateListBox(ByVal argArray As Variant)
    ' restrict user to one choice a time
    Me.ListBoxDonReportsSortField.MultiSelect = fmMultiSelectSingle
    ' populate list box
    Me.ListBoxDonReportsSortField.List = argArray
End Sub

Public Property Let CallBackMacro(ByVal argCallBackMacro As String)
    ' store macro name
    this.CallBack = argCallBackMacro
End Property

Public Property Set RangeToSort(ByVal argRangeToSort As Range)
    ' store range to sort
    Set this.RangeToSort = argRangeToSort
End Property

Within a standard module there are two separate procedures. One is responsible for:
- define the worksheet range to be sorted;
- retrieve the header texts (to be used later as a search key);
- provide the userform with the necessary data (trough the tailor made methods & properties);
- display the userform on the screen.
The second procedure is responsible for the sort logic. This one is called from the userform at the moment user double clicks on the listbox.

This goes in a standard module:

Public Sub UweNaujak()

    Const COLUMNDELIMITER As String = "*"       ' << this character may not(!!) be used in any header; change to suit

    Dim rng As Range
    Dim arr As Variant
    Dim uf  As UserForm1
    Dim i   As Long

    ' define range to work with
    With ActiveSheet                                                        ' <<< change to suit
        Set rng = .Range("A1:F" & .Cells(.Rows.Count, "A").End(xlUp).Row)   ' <<< change to suit
    End With

    ' obtain its headers and put them finally in an array memory type
    For i = 1 To rng.Columns.Count
        arr = arr & rng.Cells(1, i).Value & COLUMNDELIMITER
    Next i
    arr = VBA.Split(arr, COLUMNDELIMITER)
   
    ' omit last delimiter within array
    ReDim Preserve arr(UBound(arr) - 1)

    ' display userform with list box
    Set uf = New UserForm1

    With uf
        ' your custum method
        .PopulateListBox argArray:=arr
       
        ' custom property #1
        .CallBackMacro = "DoSomeSort"
       
        ' custom property #2
        Set .RangeToSort = rng
       
        ' native userform Show method
        .Show
    End With
End Sub


Public Sub DoSomeSort(ByVal argRange As Range, ByVal argSortKey As String)

    Dim oWs     As Worksheet
    Dim rgFind  As Range
    Dim lr      As Long

    ' determine bottom populated row of range to be sorted, row number related to worksheet
    lr = argRange.Rows(argRange.Rows.Count).Row

    ' search within first row only, in which the headers are
    With argRange.Rows(1)
        Set rgFind = .Find(What:=argSortKey, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
    End With

    If Not rgFind Is Nothing Then
        Set oWs = argRange.Parent
        With oWs.Sort
            ' clear existing Keys
            .SortFields.Clear
            ' add new Key, which is the column to be sorted from top to bottom within range to be sorted
            .SortFields.Add Key:=oWs.Range(oWs.Cells(rgFind.Row, rgFind.Column), oWs.Cells(lr, rgFind.Column)), Order:=xlAscending
            ' range to be sorted
            .SetRange argRange
            .Header = xlYes
            .Apply
        End With
    End If
End Sub

Note that the range which has to be sorted is defined in just one place within the code. Everything else that is needed to ensure that sorting runs smoothly is derived from this. This implies that the code can also be used if the range is elsewhere on the worksheet, instead starting at cell A1. Also note that no global variables are used. Data transfer takes place during the call of a procedure. This allows some errors to be discovered at compile time, rather than at run time. Finally, note that the code assumes a userform named UserForm1, which contains a list box named ListBoxDonReportsSortField.

Hopefully this helps with the further development of your project.

Thank you, gee, you put a lot of work into this. I so much appreciate your help. I will work with your material as soon as I get back into the project. My problem is that this is not my main responsibility and I have a lot of other things to take care of, which at times also makes it difficult to focus in situations like this one. I'll keep you updated as to how I'm coming along. Thanks again, ttyl

 

[GWteB]

You are welcome and thanks for letting me know.