set index for comboBox list

    [ATTACH=CONFIG]73829[/ATTACH][ATTACH=CONFIG]73828[/ATTACH]
    Hi, im trying to get the value from the combo box to filter the data in sheets as i dont use userform. but the combobox value cant be detected. i didnt know if i dont declared it as string or because of the value inside the combo box is too long. i used 2 code to add value in combo box (array & manually add . additem). When the user select the value inside combo box the ACO category will automatically filter to the selection only. [Blocked Image: https://www.mrexcel.com/forum/images/smilies/confused.png]



    im try both ways to add but the array is looping the data inside the combo box and the manual additem is error.


    im attach 2 picture to give you guys some idea.

    Re: set index for comboBox list


    Welcome to the forum!


    If you are using an ActiveX combobox control: In my example, Field 1 is your field number and cb1List is the named range for values to filter in field 1, ComboBox1 is the name of the control, right click the sheet's tab, View > Code, and paste.

    Code
    Private Sub ComboBox1_Change()
  ActiveSheet.UsedRange.AutoFilter 1, ComboBox1.Value
End Sub


Private Sub ComboBox1_DropButtonClick()
  ComboBox1.List = Range("cb1List").Value
End Sub

    Re: set index for comboBox list


    It work perfectly!!!! wow u r really amazing. how can i filter according to the combo box value?



    Quote from Kenneth Hobson;799903

    Welcome to the forum!


    If you are using an ActiveX combobox control: In my example, Field 1 is your field number and cb1List is the named range for values to filter in field 1, ComboBox1 is the name of the control, right click the sheet's tab, View > Code, and paste.

    Code
    Private Sub ComboBox1_Change()
  ActiveSheet.UsedRange.AutoFilter 1, ComboBox1.Value
End Sub


Private Sub ComboBox1_DropButtonClick()
  ComboBox1.List = Range("cb1List").Value
End Sub

    Re: set index for comboBox list


    im sorry i have one more last question,


    If the filter have category all and all is set as default. Which mean if user didnt select anything from combo box it will automatically go for "ALL". How to set "All" as default

    Re: set index for comboBox list


    Here are 3 ways. You can trigger it by one of many methods. e.g. A value for the combobox like say All and use an If(). A ThisWorkbook event like Open.


    Code
    ActiveSheet.UsedRange.AutoFilter Field:=1 'Reset field 1 to All.
    ActiveSheet.UsedRange.AutoFilter  'Toggle on/off
    ActiveSheet.UsedRange.AutoFilterMode = False  'Turn off

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login