Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill ListBox as per number of Records in the sheet.
Hi,
I have a ListBox1 which is connected to the "DailyPurchase" worksheet, I set the RowSource : DailyPurchase!A5:H2000. DailyPurchase sheet is updated daily basis at least 20 entries per day. and my DailyPurchase sheet have 200 records at present, and its increasing day by day. the problem is this when I load the userform it shows lot of blank rows at bottom in the listbox, which I dont want to see. I want to see only current entries/records in the ListBox. if I add 20 or more than 20 records per day, the listbox shows the actual records in the listbox. If any one knows how to do this pls help me out. Best Regards. Syed Shahzad Zafar Madinah |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill ListBox as per number of Records in the sheet.
Do not set the RowSource in the Properties box, set it in code instead. Try
using something like this in your UserForm_Initialize event... Dim LastRow As Long LastRow = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row ListBox1.RowSource = "A1:H" & LastRow Rick "Shazi" wrote in message ... Hi, I have a ListBox1 which is connected to the "DailyPurchase" worksheet, I set the RowSource : DailyPurchase!A5:H2000. DailyPurchase sheet is updated daily basis at least 20 entries per day. and my DailyPurchase sheet have 200 records at present, and its increasing day by day. the problem is this when I load the userform it shows lot of blank rows at bottom in the listbox, which I dont want to see. I want to see only current entries/records in the ListBox. if I add 20 or more than 20 records per day, the listbox shows the actual records in the listbox. If any one knows how to do this pls help me out. Best Regards. Syed Shahzad Zafar Madinah |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill ListBox as per number of Records in the sheet.
On Jul 1, 10:33*am, "Rick Rothstein \(MVP - VB\)"
wrote: Do not set the RowSource in the Properties box, set it in code instead. Try using something like this in your UserForm_Initialize event... Dim LastRow As Long LastRow = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row ListBox1.RowSource = "A1:H" & LastRow Rick "Shazi" wrote in message ... Hi, I have a ListBox1 which is connected to the "DailyPurchase" worksheet, I set the RowSource : DailyPurchase!A5:H2000. DailyPurchase sheet is updated daily basis at least 20 entries per day. and my DailyPurchase sheet have 200 records at present, and its increasing day by day. the problem is this when I load the userform it shows lot of blank rows at bottom in the listbox, which I dont want to see. I want to see only current entries/records in the ListBox. *if I add 20 or more than 20 records per day, the listbox shows the actual records in the listbox. If any one knows how to do this pls help *me out. Best Regards. Syed Shahzad Zafar Madinah- Hide quoted text - - Show quoted text - Dear Mr. Rick, Thank you very very much for your support, you exactly send the solution what I needed. Once again Thank you very much for your support. God Bless You. Regards. Shahzad |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill ListBox as per number of Records in the sheet.
On Jul 1, 10:33*am, "Rick Rothstein \(MVP - VB\)"
wrote: Do not set the RowSource in the Properties box, set it in code instead. Try using something like this in your UserForm_Initialize event... Dim LastRow As Long LastRow = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row ListBox1.RowSource = "A1:H" & LastRow Rick "Shazi" wrote in message ... Hi, I have a ListBox1 which is connected to the "DailyPurchase" worksheet, I set the RowSource : DailyPurchase!A5:H2000. DailyPurchase sheet is updated daily basis at least 20 entries per day. and my DailyPurchase sheet have 200 records at present, and its increasing day by day. the problem is this when I load the userform it shows lot of blank rows at bottom in the listbox, which I dont want to see. I want to see only current entries/records in the ListBox. *if I add 20 or more than 20 records per day, the listbox shows the actual records in the listbox. If any one knows how to do this pls help *me out. Best Regards. Syed Shahzad Zafar Madinah- Hide quoted text - - Show quoted text - Hi, I am using the below code in my userform, to hide unwanted blank rows in Listbox, My data is coming after filtration procedure to AP5:AY filtered data is veriable, some times 5 rows and some time 50 rows and some time 70 rows. I want to see only the filtered rows in the list box. but this procedure is not working in my new form. your procedure is working in other file, but in this file its making problem. Option Explicit Dim LastRow As Long Private Sub Userform_Initialize() Sheets("DailyIssue").Select 'for Listbox Scrol bar hide unwanted rows. LastRow = Worksheets("DailyIssue").Cells(Rows.Count, "a").End(xlUp).Row ListBox1.RowSource = "AP5:AY" & LastRow StartDate = "" EndDate = "" MaterialName = "" CoboCategory = "" TxtTRNo = "" CoboEmployee = "" StartDate.SetFocus End Sub Private Sub CommandButton1_Click() range("A4:J5000").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=range _ ("AP1:AY3"), CopyToRange:=range("AP4:AY500"), Unique:=False Selection.End(xlUp).Select Sheets("DailyIssue").Select range("AP3:AY3") = "" End Sub Can you check where is the mistake. Best Regards Syed Shahzad Zafar |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loop through sheets and get number of records per sheet | Excel Programming | |||
Populate ListBox with Filtered Records | Excel Programming | |||
Using vb to paste records from 1 sheet, to related records in another | Excel Programming | |||
Edit Add Delete Listbox Records | Excel Programming | |||
How to know the total number of records in the excel sheet | Excel Programming |