Ivan-

[Guest]

Ivan ,I wrote this question to you yesterday and it worked wonders! Thankyou. But I have one small problem with this. I wrote the problem at the bottom-
original question-
"Im curently using the following formula-Private Sub UserForm_Initialize()

Range("d1").Select
While ActiveCell.Value <> Int(Now)
ActiveCell.Offset(1, 0).Select
Wend
ListBox1.Clear
ListBox1.AddItem ActiveCell.Offset(0, 3).Value
end sub

This code written in the userform_initialize procedure is to locate the current date on the active sheet and list the text of 3 cells to the right of column "d" in the list box on the form. This works great! What Im trying to do is; using the same formula, find the same cell value, but on a diffrent sheet (Lets say "sheet2"). However I do not want that sheet ("sheet2") to show on the screen.
My question is how do you add to the code to have the cell value on "Sheet2" show in the list box, but have the userform stay on the active sheet("sheet1") and not show "Sheet2"? "
Your response-
One way to do this;

Dim Lb2, x As Double
x = 1

With Sheets("Sheet2")
While Lb2 <> Int(Now)
Lb2 = .Range("D1").Offset(x, 0)
x = x + 1
Wend
ListBox2.Clear
ListBox2.AddItem .Range("D1").Offset(x - 1, 3).Value
End With


HTH

Ivan"
My attached question-

That userform that displays the list box(there are 8 listboxes pulling data from diffrent cells).But one of the list boxes does not use the offset method. I have to list a range (which identifies a sum 7 columns to the right of 31 dec 2002), but once the next year takes affect then I need that same list box to read a new sum in a cell that reads 7 cells to the right of 31 Dec 2003.
My question would be how to write a code so that the list box knows once the end of the year is up, how can it then read the value in the next range?

 

[Ivan F Moala]

To check the date do something like;
the following before populating the list

If Date > CDate("31/12/2003") Then
'somecode if past the date
Else
'other code if not
End If


Ivan