Drop down box in form in Excel for user input

[haplc]

Dear All,

I am using an input box to get the user input. With the user input, I am setting autofilter criteria. Here is the code:

Dim SelSeg As String

ActiveWorkbook.Sheets("Jan 2015").Activate
Range("a1").Select

SelSeg = InputBox("Please enter name of segments for which you are looking for stories. For all segment, leave the input blank ")

If (SelSeg <> "") Then

Rows("1:1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$AE$7").AutoFilter Field:=1, Criteria1:="" & SelSeg & ""
Else
ActiveSheet.Range("$A$1:$AE$7").AutoFilter Field:=1
End If
End Sub

Now, I would like to offer user form so that the user can select the input from the drop down box in the form. Also, the list in dropbox should be from Range A1:A4 (or last row of the Coloum A.
I have tried following code which does not work

ActiveWorkbook.Sheets("Jan 2015").Activate
Range("a1").Select

Myform.Show
Myform.AAA.List = Worksheets("Jan 2015").Range("a2:a5").Value


SelSeg = Myform.AAA.Value

If (SelSeg <> "") Then

Rows("1:1").Select
Selection.AutoFilter

ActiveSheet.Range("$A$1:$AE$7").AutoFilter Field:=1, Criteria1:="" & SelSeg & ""
Else
ActiveSheet.Range("$A$1:$AE$7").AutoFilter Field:=1
End If

End Sub

Please help: thanking you in advance
BR
haplc

 

[Norie]

Where do you have that code?

As far as I can see this part of the code will only get executed after the userform is unloaded/hidden

Myform.AAA.List = Worksheets("Jan 2015").Range("a2:a5").Value


SelSeg = Myform.AAA.Value

If (SelSeg <> "") Then

Rows("1:1").Select
Selection.AutoFilter

ActiveSheet.Range("$A$1:$AE$7").AutoFilter Field:=1, Criteria1:="" & SelSeg & ""
Else
ActiveSheet.Range("$A$1:$AE$7").AutoFilter Field:=1
End If

That's because when you show a userform code execution passes to it.

The code to populate the combobox on the userform should go in the userform's Initialize (or Activate) event.

Private Sub UserForm_Initialize()
    Myform.AAA.List = Worksheets("Jan 2015").Range("a2:a5").Value
End Sub

Not sure when you want the filter run but you might want to add a command button to the userform and put the filtering code in it's click event.

 

[haplc]

Simply Great..thanks a lot. It works.

You are right, I was putting the code in the macro and not inthe form.
So what I did now, is placed the code for getting the value in form (dropbox) and then the code to execute in the command button

One more question:

Can you also suggest a methid to select the range till last row before empty row?

Thanks again

 

[Norie]

For the combobox?

Private Sub UserForm_Initialize()
    With Worksheets("Jan 2015")
        Myform.AAA.List = .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Value
    End With
End Sub

 

[haplc]

Thanks buddy
Yes, this is for Combobox.
Works well