Hi,
I require some help using a combo box in Excel. I wish to use a
combo box to select a number from a list of about 10 and then go to
another worksheet in the book and filter a column based on the number
selected in the combo box.
I have got as far as populating the combo box with the required
numbers, but stuck with the VBA code needed to take the figure and
then filter the column with it.
If the combo box is in sheet1 and the column that needs to be filtered
in column A of sheet 2, what VBA code would i need to do this?

Thanks in advance.

Hi,

Try this - assume C1 on Sheet1 contains the combobox value::

CritValue = Worksheets("Sheet1").Range("C1") ' Set to your value from the
combobox

Worksheets("Sheet2").Columns("A:A").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=CritValue

' Or

Selection.AutoFilter Field:=1, Criteria1:=Worksheets("Sheet1").Range("c1")

HTH


"Thomas Earnshaw" wrote:

Hi,
I require some help using a combo box in Excel. I wish to use a
combo box to select a number from a list of about 10 and then go to
another worksheet in the book and filter a column based on the number
selected in the combo box.
I have got as far as populating the combo box with the required
numbers, but stuck with the VBA code needed to take the figure and
then filter the column with it.
If the combo box is in sheet1 and the column that needs to be filtered
in column A of sheet 2, what VBA code would i need to do this?

Thanks in advance.

I put a combobox from the control toolbox toolbar on a worksheet.

I used this code behind that worksheet:

Option Explicit
Private Sub ComboBox1_Change()
With Worksheets("sheet2")
If .AutoFilterMode Then
If .FilterMode Then
.ShowAllData
End If
If Me.ComboBox1.Value < "" Then
.AutoFilter.Range.AutoFilter field:=1, _
Criteria1:=Me.ComboBox1.Value
End If
Else
MsgBox "Please apply autofilter to sheet2!"
End If
End With
End Sub


I used sheet2 and column 1 of the filtered range (already applied).

Thomas Earnshaw wrote:

Hi,
I require some help using a combo box in Excel. I wish to use a
combo box to select a number from a list of about 10 and then go to
another worksheet in the book and filter a column based on the number
selected in the combo box.
I have got as far as populating the combo box with the required
numbers, but stuck with the VBA code needed to take the figure and
then filter the column with it.
If the combo box is in sheet1 and the column that needs to be filtered
in column A of sheet 2, what VBA code would i need to do this?

Thanks in advance.

--

Dave Peterson

Thanks Toppers,
This Works great!


Toppers wrote:
Hi,

Try this - assume C1 on Sheet1 contains the combobox value::

CritValue = Worksheets("Sheet1").Range("C1") ' Set to your value
from the
combobox

Worksheets("Sheet2").Columns("A:A").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=CritValue

' Or

Selection.AutoFilter Field:=1,
Criteria1:=Worksheets("Sheet1").Range("c1")

HTH


"Thomas Earnshaw" wrote:

Hi,
I require some help using a combo box in Excel. I wish to use a
combo box to select a number from a list of about 10 and then go to
another worksheet in the book and filter a column based on the
number
selected in the combo box.
I have got as far as populating the combo box with the required
numbers, but stuck with the VBA code needed to take the figure and
then filter the column with it.
If the combo box is in sheet1 and the column that needs to be
filtered
in column A of sheet 2, what VBA code would i need to do this?

Thanks in advance.