combox value change selection

[vmjan02]

I have a userform and a combox in the userform. the combox ragne is autofilled from sheet 1 range c3 till the last record.
I am not sure,

If the user selects any record from me.combox then the code should do autofilter from c3:aa500, (c3 to aa3 are header) and then should stop at the visible row and then should select aa column visible record.

I have do lot of google and tried a lot but no luck.

Any idea of help.

 

[Yongle]

try this


put VBA in userform's module

Private Sub [COLOR=#000080]ComboBox1[/COLOR]_Change()
    Call GoToCellAfterFilter([COLOR=#000080]ComboBox1[/COLOR].Value)
    Unload Me
End Sub

Private Sub GoToCellAfterFilter(myCriteria As String)
    Dim rng As Range
    With Sheets("[COLOR=#ff0000]Data[/COLOR]")
        .Activate
        Set rng = .Range("C3:AA500")
        On Error Resume Next
        .ShowAllData
        .rng(1).AutoFilter
        rng.AutoFilter Field:=[COLOR=#ff0000]1[/COLOR], Criteria1:=myCriteria
        .Range(Split(Split(rng.SpecialCells(xlCellTypeVisible).Address, ",")(1), ":")(1)).Select
    End With
End Sub

notes
Amend sheet & ComboBox names
The filter matches a string
The macro filters on column C which is column 1 in your range(C:AA) - amend if necessary

 

[vmjan02]

perfect all working good.
but the only problem is the combox list is not populating all the records. I mean,

a) if the user selects "x" the code works all good, but the userform combox then only shows "x" and lot rest of the details
b) it should only select the visible cell column AA only. currently it selects cell "AA3" if the user. if visible cell is row num 8 then the code should select aa8, if the row is 11 visible then the aa11 should be selected.

 

[Yongle]

I do not understand what you are asking

 

[vmjan02]

no problem solved it. thanks