none
Creating Filters on the fly with UserForm inputs RRS feed

  • Question

  • I am trying to create a custom filter based on input from a userform.  Tasks in the schedule are coded with a value in the Text 22 field.  I'd like the filter to show tasks with particular Text22 values based on check box input.  I am very new to VBA, though, so am not sure if I'm going about this the right way.  Here is what I have:

    Option Explicit
    
    Sub InitializeME()
     'Create and label form elements
     Label1.Caption = "Please Select Manufacturing Sites:"
     Label2.Caption = "Form A"
     Label3.Caption = "Form B"
     CheckBox1.Caption = "CM #1"
     CheckBox2.Caption = "CM #2"
     CheckBox3.Caption = "CM #3"
     CheckBox4.Caption = "CM #4"
     CommandButton1.Caption = "Apply"
     
     'Format form elements
     Label1.Width = 150
     Label1.Height = 36
      
     'Initialize values for form elements
     CheckBox1.Value = True
     CheckBox2.Value = True
     CheckBox3.Value = True
     CheckBox4.Value = True
     
    End Sub
    
    ''''''''''''''''''''''''''''
    
    Private Sub CommandButton1_Click()
    Dim Codes(4)
    Dim Code
    
    If CheckBox1.Value = False Then
        Codes(0) = "CM1"
    End If
    
    If CheckBox2.Value = False Then
        Codes(1) = "CM2"
    End If
    
    If CheckBox3.Value = False Then
        Codes(2) = "CM3"
    End If
    
    If CheckBox4.Value = False Then
        Codes(3) = "CM4"
    End If
    
    'Takes codes set by CheckBox Values in UserForm and creates a custom filter from them
    
    OutlineShowAllTasks
    
    For Each Code In Codes
       FilterEdit Name:="_Toggle", TaskFilter:=True, _
       Create:=True, _
       OverwriteExisting:=True, _
       FieldName:="Text22", _
       Test:="Contains", _
       Value:=Code, _
       Operation:="Or", _
       ShowInMenu:=True
    Next Code
    
    FilterApply Name:="_Toggle"
    Sort Key1:="Start", Outline:=True
        
    End Sub
    

    There are probably many more elegant ways to go about this - any help would be much appreciated!

    Saturday, September 3, 2011 11:47 AM

All replies

  • If it works that way, then OK. I'd read all the values into a listbox which allows multiple selections. Then take the selections from the list and use them when you build the filter.

    But, why not just turn on autofilter?


    Jack Dahlgren blogs at:
    Project and Retrovention
    and rarely Twitter
    Saturday, September 3, 2011 1:08 PM
    Moderator
  • Well it does not work that way (I forgot this minor detail).  I get an error:  "Runtime error '1101':  the argument value is not valid" and debugging shows this: 

     

    As far as using autofilter goes, well, that is what I would do!  This is for a template used by program managers with very limited MS Project experience.  The template has thousands of tasks and is customized by hiding tasks which do not apply to a given project.  The userforms above will be part of a procedure which will walk the users through customizing the template to a given project. 


    • Edited by krcullen Saturday, September 3, 2011 1:39 PM
    Saturday, September 3, 2011 1:36 PM