Using a listbox to list a cell value

Todd_M

Board Regular
Joined
Feb 24, 2002
Messages
117
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"?
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,524
Messages
6,114,117
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top