How to display in the listbox based on Combo-Box selection

  • Hi everyone.

    I have a user-form which contains a Combo-Box and a listbox

    The combo-Box is initialized with my excel worksheet (4 of them). How do I make the list-box display the data based on the combo-box selection

  • Do you mean to work with a specific sheet? If so try this

  • You need to attach a workbook not an image


    Okay.


    I have being able to populate the list-box based on the selection of the combo-box.


    I however have a problem whereby for any display on the list-box, the actual related sheet always shows up.


    is there a way one can prevent the actual sheet from showing up anytime the list-box is being populated.


    Kindly see the attached file

  • Your workbook is not designed properly for, read this for a guidelin.


    working with data in Excel


    You data should have no completely empty rows or columns like rows 4 and 5 in your data.


    All data is best kept in one sheet, with a column for region. There are may built-in tools in Excel to work with that data.

  • The workbook has been designed to Track different regions performance and I don't think the company would accommodate having all its data in one sheet. I am constrained to work this way.


    The empty row is used to do inline search through the data.


    I did mention that, I have been able to display each worksheet in the list-box based on the selection of the combo-box, my only problem is that, while the data is displayed in the list-box the associated worksheet is also activated in the background.

    So I was asking if there is a way to prevent the actual worksheet from getting triggered (showing in the background) whenever the list-box is displaying the data.

  • Don't activate the sheet then. Your code is doing what you tell it to


    Code
    1. Worksheets(TargSheet).Activate

    You will continue to have problems if you ignore my comments.


    Quote


    The empty row is used to do inline search through the data.

    That is nonsense, the empty row simply makes the data less usable.


    The company needs to consult someone that knows what they are doing. If it is important then it needs to be coded properly not pieced together by using Excel Forums.


    I can see several issues that really need resolving.

  • When I implement the code by commenting on it

    Code
    1. 'Worksheets(TargSheet).Activate

    The Worksheet triggering stopped but I got a static display on the list-box, it will not change for different selection in the combo-box




    I'm completely learning through this work and willing to learn better. Please pardon my inexperience

  • Change to this


    Code
    1. .RowSource = Worksheets(Cmb_Database.Value).Range("D3:U250").Address(external:=True)

    Do you realise the code is picking up more than the data. Using a fixed range is bad practice.

  • Change to this


    Code
    1. .RowSource = Worksheets(Cmb_Database.Value).Range("D3:U250").Address(external:=True)

    It works perfectly. Thanks royUK

    Quote


    Do you realise the code is picking up more than the data. Using a fixed range is bad practice.

    Yes, I did that just to accommodate the additional of few new data which is even way to much based on the nature of these regions.




    Learning it the better way based on your observation, how do I implement it without using fixed range?

  • That's difficult because your data is not set up correctly. Try this

  • Trying this as stated

    Code
    1. Private Sub Cmb_Database_Change()
    2. If Me.Cmb_Database.ListIndex < 0 Then Exit Sub
    3. Set TargSheet = Worksheets(Cmb_Database.Value)
    4. With Me.List_Database
    5. .RowSource = ""
    6. .ColumnCount = 18
    7. .ColumnWidths = "30;60;100;40;60;60;60;30;30;30;30;30;30;40;40;49.95;40;49.95"
    8. .RowSource = TargSheet.Range(TargSheet.Cells(6, 4), TargSheet.Cells(TargSheet.Rows.Count, 21).End(xlUp)).Address
    9. End With
    10. End Sub

    I got this error