Trying this as statedCode
- 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
It works perfectly. Thanks royUKQuote
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?
When I implement the code by commenting on it
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
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.
I'm completely learning through this work and willing to learn better. Please pardon my inexperience
Your workbook is not designed properly for, read this for a guidelin.
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.
Please help me out.
Yusuf, read the Forum Rules before posting again
Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post. We are here to help so help us help you!
Readthis to understand why we ask you to do this
Okay, I have taken proper note and done the needful.
MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question
I have 4 working sheets in my excel file. I have a user-form which the user can search by using any of the input field and the result will be displayed in the list-box found below it
The first worksheet will serve as the launch interface while the remaining 3 sheets are the actual data worksheets
How do I search from the user-form through the 3 worksheets and have the result displayed in the list-box
You need to attach a workbook not an image
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
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.
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
Welcome to the Forum. Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post
All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.
How to use code tags
Note: no apostrophe in the tags, just used for demonstration here.
your code goes between these tags
Or, just highlight all of the code and press the <> in the post menu above button to add the code tags.
I have a user-form with a Combo-Box and 6 text fields and 3 buttons( Update, Reset, Exit). My Combo-Box contains all the worksheets( i have 4 of it) as it contents
How do I ensure that after updating my desired worksheets, the updates always falls in the new rows.
My subsequent updates always overrides the previous update and it defeating the idea of updating from the user-form
This is my update button codeCode
- Private Sub Cmd_Update_Data_Click()
- TargetSheet = Cmb_Application.Value ' Cmb_Application is the name of my Combo-Box
- If TargetSheet = "" Then
- MsgBox ("Please, Select the Region for the data update")
- Exit Sub
- End If
- Dim LastRow As Long
- LastRow = ActiveSheet.Range("D999999").End(xlUp).Row + 1
- ActiveSheet.Range("E" & LastRow).Value = Txt_Site_ID.Value 'Change accordingly
- ActiveSheet.Range("F" & LastRow).Value = Txt_Site_Address.Value
- ActiveSheet.Range("G" & LastRow).Value = Txt_CPD.Value
- ActiveSheet.Range("M" & LastRow).Value = Txt_DSD.Value
- ActiveSheet.Range("H" & LastRow).Value = Txt_SD.Value
- ActiveSheet.Range("I" & LastRow).Value = Txt_ED.Value
- MsgBox ("The data has been updated to " & TargetSheet & " sheet")
- End Sub