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

Quote from royUK

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

I intend something like this.

From the user-form, any of the content of the combo-box selected ( which is actually worksheets) , the list-box should display the data.

Quote from royUK

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

Quote from royUK

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.

Display More

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.

When I implement the code by commenting on it

'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

Quote from royUK

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

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.

Display More

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

Quote from royUK

Change to this

Code

.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?

Trying this as stated

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

I got this error

Quote from royUK

This works for me

Yes , the initial file had.

Dim TargSheet As String

So it now works by setting.

Dim TargSheet As Worksheet

Thanks a lot royUk.