Using a listbox to list a cell value

[Todd_M]

Hi-
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"?

 

[mrowe]

This might be a bit clunky, but I did something similar recently where I hid sheet 2, but still referenced the cell....Not the greatest solution but it worked.

 

[Ivan F Moala]

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

 

[Todd_M]

Thanks so much Ivan, this work great!!
By the way, 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?