Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: How can you change autofilter with VBA?

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have a userform, frmrelease, that has two comboboxes, cmbcontrolcode & cmbjobnum.

    cmbjobnum is bound to sheet "release", column A. cmbcontrolcode is bound to column B.

    Here's a mock table....
    ~~~~~~~~~~~~~~~~~~~~~~~
    Column A Column B
    123 AAA
    456 BBB
    123 CCC
    456 DDD
    123 EEE
    456 FFF
    ~~~~~~~~~~~~~~~~~~~~~~

    Now, what I need to accomplish is this. If cmbjobnum is "123", then only "AAA", "CCC" & "EEE" populate cmbcontrolcode. Likewise, if cmbjobnum is "456", then only "BBB", "DDD" & "FFF" populate cmbcontrolcode.

    So far, this is what I have (thanks to Babytiger), but it's not working...

    Selection.AutoFilter Field:=0, Criteria1:=Sheets("release").cmbjobnum.value

    Any Ideas??

    Thanks

  2. #2
    New Member
    Join Date
    Mar 2002
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    anyone??

    thanks

  3. #3
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi VTTW,

    I haven't researched this carefully, but I think the problem is simply the Selection qualifier for the Autofilter object, which is supposed to be a worksheet. Give this a try:

    With Worksheets("release")
    .AutoFilter Field:=0, Criteria1:=.cmbjobnum.value
    End With


    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

  4. #4
    New Member
    Join Date
    Mar 2002
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I've tried it, but I'm getting a run time error. Here's what I have....
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~`
    Private Sub UserForm_Activate()
    Me.cmbjobnum = Main.MAINjobnumberlist
    With Worksheets("release")
    .AutoFilter Field:=0, Criteria1:=.cmbjobnum.Value
    End With
    End Sub
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    Can you see what would give a run time error??

    Thanks for your input.


  5. #5
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi again VTTW,

    I goofed. I thought you combobox was on the worksheet rather than a userform. So my statement

    .AutoFilter Field:=0, Criteria1:=.cmbjobnum.Value

    should have been

    .AutoFilter Field:=0, Criteria1:=Me.cmbjobnum.Value

    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •