Excel

Fill listbox with redimensionable array

Ease of Use

Intermediate

Version tested with

2003 

Submitted by:

Charlize

Description:

Populate a listbox with three columns. The current day must fall in between the start en end dates. 

Discussion:

When you need to know when people may bid for something (house, auction) between two dates you can use this to quickle identify the items that fall in between or are equal to the current date. 

Code:

instructions for use

			

Place the following In a module : Option Explicit Sub in_between_or_not() 'run this to hide the unwanted data Dim startrow As Integer 'Start of the data Dim endrow As Integer 'End of data Dim actualrow As Integer 'rownumber during loop from start to end Dim counting As Integer startrow = 11 'always 11 endrow = LastCell(Dates_for_bids).Row 'lastcell is a function actualrow = 11 For counting = startrow To endrow 'as long as the endrow isn't reached, go on 'check the dates if current date is equal or in between If Date >= Range("B" & actualrow) And Date <= Range("C" & actualrow) Then 'move on to the next row actualrow = actualrow + 1 Else 'if current date isn't in between or equal than hide Rows(actualrow).EntireRow.Hidden = True 'hide row if not in between dates actualrow = actualrow + 1 End If Next counting End Sub Sub show_everything() 'run this to show back all your data Dim startrow As Integer Dim endrow As Integer Dim actualrow As Integer Dim counting As Integer startrow = 11 endrow = LastCell(Dates_for_bids).Row actualrow = 11 For counting = startrow To endrow If Rows(actualrow).Hidden = True Then Rows(actualrow).EntireRow.Hidden = False actualrow = actualrow + 1 Else actualrow = actualrow + 1 End If Next counting End Sub Function LastCell(ws As Worksheet) As Range Dim LastRow&, LastCol% ' This is a function from someone else that I found on his or her site. ' Use it to know the last used cell in a sheet. ' Error-handling is here in case there is not any ' data in the worksheet On Error Resume Next With ws ' Find the last real row LastRow& = .Cells.Find(What:="*", _ SearchDirection:=xlPrevious, _ SearchOrder:=xlByRows).Row ' Find the last real column LastCol% = .Cells.Find(What:="*", _ SearchDirection:=xlPrevious, _ SearchOrder:=xlByColumns).Column End With ' Finally, initialize a Range object variable for ' the last populated row. Set LastCell = ws.Cells(LastRow&, LastCol%) End Function --- End For module This Is the code For the form With one listbox on it Option Explicit Private Sub UserForm_activate() Dim R As Integer 'loop going through rows Dim rijteller As Integer 'the rownumber Dim pos As Integer 'place in the array Dim MyList() As String 'name of the array Dim i As Integer 'no of rows in the array that you don't know Dim echte_laatste_rij As Integer 'no of the last row without filtering echte_laatste_rij = LastCell(Dates_for_bids).Row 'determine the last row 'last row must be known before you are going to filter 'so first you count the no of rows and then you hide the rows based 'on the criteria. in_between_or_not 'this is the code that filters the rows to be seen. rijteller = 11 'start of the data For R = 11 To echte_laatste_rij 'Loop trough all the rows to determine how much rows the array must have If Rows(rijteller).Hidden = True Then 'if row is filtered, go to next row rijteller = rijteller + 1 'row is row+1 Else i = i + 1 'if not hided, array is one row bigger rijteller = rijteller + 1 'go to the next row End If Next R ReDim Preserve MyList(i, 3) 'Here you must have the number of rows that aren't hidden. 'the list contains 3 columns 'you can adjust the number, width and height Application.ShowToolTips = True With ListBox1 .ColumnCount = 3 .ColumnWidths = "2 cm ;2 cm;5 cm" .ControlTipText = "Dates for bids ..." .ListStyle = fmListStylePlain .SpecialEffect = fmSpecialEffectFlat End With 'Define the list and where it's obtained from (Columns B, C, D in this example) rijteller = 11 pos = 0 'place in redefined array - MyList(i,3) so many i rows and 3 columns With ActiveSheet For R = 0 To echte_laatste_rij - 10 '-10 because data starts at row 11 If Rows(rijteller).Hidden = True Then rijteller = rijteller + 1 Else MyList(pos, 0) = .Range("B" & rijteller) 'value of cell B in array MyList(pos, 1) = .Range("C" & rijteller) MyList(pos, 2) = .Range("D" & rijteller) pos = pos + 1 'arrayrow is 1 higher rijteller = rijteller + 1 'row is one higher End If Next R End With 'fill the listbox with the array ListBox1.List = MyList End Sub Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) show_everything 'restore worksheet to show all the rows Unload Me 'remove form from memory End Sub

How to use:

  1. open vba editor with alt+f11
  2. copy the code for the module to a module (maybe you have to right click on the right side of your screen - project screen - and insert a module)
  3. isert a form in the same way and put a listbox on it
  4. right click on the listbox and choose view programcode
  5. copy the section for the form userform.activate
  6. if you want to alter the beginning of the data be sure to adjust the variables dealing with positioning throught the rows (startrow, actualrow in module)
  7. variables in form : rijteller
  8. if you alter the columns you have to change it accordingly
 

Test the code:

  1. click the button on the sheet and you'll see an example
  2. data starts from row 11 in column b.
  3. columns b and c must be filled in (no error check yet).
  4. startdate = date for bidding, enddate = date for stopping, text = text
  5. insert dates at will. I used text to see if it's ok or not.
 

Sample File:

VBA-Express - Hide row based on dates.zip 18.83KB 

Approved by mdmackillop


This entry has been viewed 465 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2014 VBA Express