Posts by Yusuf_

Quote from royUK

You expect help when you haven't responded to your previous post!!!

Hello royUK.

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.

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

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?

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

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.

Please help me out.

Quote from royUK

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

Display More

Okay, I have taken proper note and done the needful.

Quote from rollis13

Added Cross-posting Link

Oops, is the forum the same?

[xpost][/xpost]

Hi everyone.

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

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

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.

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

Thanks, I have worked it out

It happens that my LastRow was wrong

LastRow = ActiveSheet.Range("D999999").End(xlUp).Row + 1

This fixed it

LastRow = ActiveSheet.Range("E999999").End(xlUp).Row + 1

Thanks.

Quote from royUK

That's what your code should be doing. Is there any entries below the data?

I have no entries below the data.

Quote from royUK

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.

['code]

your code goes between these tags

['/code]

Or, just highlight all of the code and press the <> in the post menu above button to add the code tags.

Thanks.

Display More

Okay. Thanks

Hi everyone.

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 code

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


Worksheets(TargetSheet).Activate


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

Please help