How to use variables in a Userform?

[KimberlyHeart]

<tbody>
[TD="class: postcell"] I have a spreadsheet with 1000+ rows of data, that I want to filter down based on two separate columns of values. I know I can do this using the AutoFilter option, but I am trying to do this using an Excel VBA script which prompts the user for the values it wants to filter on. So, I have a way for identifying the unique values available in each column I want to filter on:

Public Sub MakeFilter()
    Dim Makes As Variant
    Application.ScreenUpdating = False

'Get a temporary list of unique values from Make (column H)
    ActiveSheet.Columns("H:H").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=ActiveSheet.Range("EE1"), Unique:=True


'Sort the temporary list
    ActiveSheet.Columns("EE:EE").Sort Key1:=ActiveSheet.Range("EE2"), Order1:=xlAscending, Header:=xlYes, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal


'Put list into an array for looping
    Makes =  Application.WorksheetFunction.Transpose(ActiveSheet.Range("EE2:EE" &  Rows.Count).SpecialCells(xlCellTypeConstants))


'clear temporary worksheet list
    ActiveSheet.Range("EE:EE").Clear

     D<code>im txt As String, k As Integer
        For k = 1 To UBound(Makes)
            txt = txt & Makes(k) & ", "
        Next
     MsgBox "Unique values:" & vbCrLf & txt
</code>End Sub

This works... and I get the message box with the values I want. So, my array "Makes" is holding the values I need.


I also have created a UserForm with a ListBox where I want to display these unique values and let the user multi-select which values they want to filter on.

How do I get the values from my previously created array to display in this UserForm/ListBox? I was trying this as the initial code inside the UserForm1:

Private Sub UserForm_Initialize()
    Dim i As Integer

    With ListBox1
        For i = LBound(Makes) To UBound(Makes)
            ListBox1.AddItem Makes(i)
        Next i
    End With
End Sub

But, for some reason, when I try to execute this code, via:

UserForm1.Show

which is the line immediately after the MsgBox line above, I get "ERROR 13 TYPE MISMATCH" with that UserForm1.Show line highlighted.

WHAT IS WRONG THERE?



Once the selections are made, how do I capture only those selected values and then apply the filter to my spreadsheet? Part of my code here is:

' Filter by Make
     ActiveSheet.Range("A:N").AutoFilter Field:=8, Criteria1:=Array("FORD", "CHEVY", "DODGE", _
    "NISSAN", "TOYOTA", "KIA", "LINCOLN", "CADILLAC", "MAZDA"), Operator:=xlFilterValues

But, that is with the hardcoded values.
How do I get the user selected values from the Makes array to be used in this filter?
[/TD]

</tbody>

 

[dmt32]

Hi,
Try changing your Sub to a Function to return the array to populate your Listbox:

Standard Module:

Function MakeFilter() As Variant


'Get a temporary list of unique values from Make (column H)
    ActiveSheet.Columns("H:H").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=ActiveSheet.Range("EE1"), Unique:=True




'Sort the temporary list
    ActiveSheet.Columns("EE:EE").Sort Key1:=ActiveSheet.Range("EE2"), Order1:=xlAscending, Header:=xlYes, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal




'Put list into an array for looping
    MakeFilter = Application.WorksheetFunction.Transpose(ActiveSheet.Range("EE2:EE" & Rows.count).SpecialCells(xlCellTypeConstants))




'clear temporary worksheet list
    ActiveSheet.Range("EE:EE").Clear


End Function

Forms code Page:

Private Sub UserForm_Initialize()
    With Me.ListBox1
        .List = MakeFilter
        .MultiSelect = fmMultiSelectMulti
    End With
End Sub

Sample code to populate array from selections:

Private Sub CommandButton1_Click()
    Dim Makes() As Variant
    Dim i As Integer, count As Integer
     
    count = 1
    For i = 0 To ListBox1.ListCount - 1
        If Me.ListBox1.Selected(i) Then
            ReDim Preserve Makes(1 To count)
            Makes(count) = ListBox1.List(i)
            count = count + 1
        End If
    Next i
    
    For i = 1 To UBound(Makes)
    
        MsgBox Makes(i)
        
    Next i


End Sub

Hope Helpful

Dave

 

[KimberlyHeart]

Thank you Dave!

I managed to get the first part working by declaring the Makes array as a Global variant, and using the code below:

' Identifying the unique values in Make column
    MakeFilter Makes

    For p = LBound(Makes) To UBound(Makes)
        UserForm1.ListBox1.AddItem Makes(p)
    Next p
    
    UserForm1.Show

Your suggestion is more efficient, so I may try the function approach, but my code for that is working at the moment.

My Userform now loads the selections into ListBox1, and the user can make their selection, and the Add button will move those selections to ListBox2.

The user can Add/Remove selections all they want, and when they are finished, they choose the DONE button.
I tried to copy your code into this DONE CommandButton area so it looks like this:

Private Sub CommandButton3_Click()
    UserForm1.Hide
    
    Dim MakeSelect() As Variant
    Dim i As Integer, count As Integer
     
    count = 1
    For i = 0 To ListBox1.ListCount - 1
        If Me.ListBox1.Selected(i) Then
            ReDim Preserve MakeSelect(1 To count)
            MakeSelect(count) = ListBox1.List(i)
            count = count + 1
        End If
    Next i
    
    For i = 1 To UBound(MakeSelect)
    
        MsgBox MakeSelect(i)
        
    Next i
End Sub

And I see the MsgBox outputs for each selection made. However, when the code goes back to the Main Module to continue...

(PARTIALLY DUPLICATED FROM ABOVE)

    For p = LBound(Makes) To UBound(Makes)
        UserForm1.ListBox1.AddItem Makes(p)
    Next p
    
    UserForm1.Show

    For i = 1 To UBound(MakeSelect)
        MsgBox "Main " & MakeSelect(i)
    Next i

...I am getting an error message Type Mismatch (13) on the "For i = 1 to UBound(MakeSelect)" statement after the UserForm.
I also declared MakeSelect as a Global variable too, so it should just be re-displaying the selections, but the values don't seem to be returning?

 

[KimberlyHeart]

Sorry! The error is "Run-time error '9', Subscript Out of Range "

 

[dmt32]

are you able to place copy of your workbook in a dropbox?

Dave

 

[KimberlyHeart]

Unfortunately, no. It belongs to a private company.

I will keep trying to figure it out.

 

[Akuini]

This is one way to populate a Listbox list from a range (e.g col H, starting from H2 to the last data row).
It creates a unique list but not sorted. But if you've already sorted the data by column H then it is sorted too. If not, it can be sorted later using some code to sort an array.

Private Sub UserForm_Initialize()
Dim d As Object
    
    Set d = CreateObject("scripting.dictionary")
    For Each r In Range("H2", Cells(Rows.Count, "H").End(xlUp))
        d(r.Value) = 1
    Next
  
    ListBox1.List = d.Keys

End Sub

 

[FDibbins]

x-posted here...
Using Variables in a Userform

 

[dmt32]

Unfortunately, no. It belongs to a private company.

I will keep trying to figure it out.

Its a little difficult to try & figure out what the issue is from code snippets provided.
Whilst it should not be necessary & best avoided, you say that you made the Variable Public (Global) but have you declared this- in the forms code page? if so, you need to move it to a standard module for it to make it available to other procedures in your project.

Dave