Using a listbox to list a cell value
Using a listbox to list a cell value
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Using a listbox to list a cell value

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Posts
    117
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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




  2. #2
    Board Regular
    Join Date
    Feb 2002
    Posts
    232
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  3. #3
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Posts
    117
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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?

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com