Combo Box
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. |
Combo Box
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. |
Combo Box
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 |
Combo Box
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. |
All times are GMT +1. The time now is 11:38 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com