Results 1 to 11 of 11
  1. #1
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    unknown range (Excel 2000)

    Hi all,

    I have a range of values in a worksheet that the user inputs a value. Here's my question, I don't know where on the sheet this range is, it changes every day. What I would like to able to do is have the user input the data, and another user change the background colour to indicate it's the status of the data, in the unknown range.

    What I would like to accomplish is this, if the first name in the array is always the same(title), how would I first find that array of information? THEN display the information like I have here in this sub? Is there a built in function that I could use for this to find it?


    Dim arrNames()
    Range(Cells(4, 3), Cells(4, 3)).Select

    Do Until Range(Cells(4 + I, 3), Cells(4 + I, 3)).Value = ""
    ReDim Preserve arrNames(I)
    arrNames(I) = Range(Cells(4 + I, 3), Cells(4 + I, 3)).Value
    I = I + 1
    Range(Cells(4 + I, 3), Cells(4 + I, 3)).Select
    Loop

    UserForm1.ListBox1.Clear
    For I = 0 To UBound(arrNames)
    UserForm1.ListBox1.AddItem arrNames(I)
    Next
    UserForm1.Show


    End Sub
    Thanks, Darryl.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: unknown range (Excel 2000)

    Something like this?

    Dim arrNames()
    Dim i As Long
    Dim oCell As Range

    ' don't forget to substitute the text you are searching for.
    Set oCell = ActiveSheet.Cells.Find(What:="Title", LookIn:=xlValues, LookAt:=xlWhole)
    If oCell Is Nothing Then
    MsgBox "Value not found", vbExclamation
    Exit Sub
    End If

    i = 0
    Do Until oCell.Offset(i, 0).Value = ""
    ReDim Preserve arrNames(i)
    arrNames(i) = oCell.Offset(i, 0).Value
    i = i + 1
    Loop

  3. #3
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: unknown range (Excel 2000)

    Thank you Hans

  4. #4
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: unknown range (Excel 2000)

    Hans Thank you ---- How would I show three different strings in three different listboxs?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: unknown range (Excel 2000)

    Can you elaborate on what you want to do?

  6. #6
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: unknown range (Excel 2000)

    OCell as range...If I were to have three seperate lists, displayed in three seperate listboxes where would I what doI need to do with this code so that I could accomplish that?

    Do I need to Redim seperate ranges,





    Dim arrNames()
    Dim i As Long
    Dim oCell As Range

    Set oCell = ActiveSheet.Cells.Find(What:="String", LookIn:=xlValues, LookAt:=xlWhole)
    If oCell Is Nothing Then
    MsgBox "Value not found", vbExclamation
    Exit Sub
    End If
    i = 0
    Do Until oCell.Offset(i, 0).Value = ""
    ReDim Preserve arrNames(i)
    arrNames(i) = oCell.Offset(i, 0).Value
    i = i + 1
    Loop
    UserForm1.ListBox1.Clear
    UserForm1.ListBox2.Clear -----IS this right?
    UserForm1.ListBox3.Clear

    For i = 0 To UBound(arrNames)
    UserForm1.ListBox1.AddItem arrNames(i)
    UserForm1.ListBox1.AddItem arrNames(i) --------------I want to display three different ranges, can it be done?
    UserForm1.ListBox1.AddItem arrNames(i)
    Next
    UserForm1.Show
    End Sub

    Can it be done?

    Thanks, Darryl.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: unknown range (Excel 2000)

    The current code finds a specific word ("String"), then puts the contents of that cell and the cells below it into an array, and uses that array to populate a list box. Now, you want to populate 3 list boxes. Where do you want to populate them from? Should they all have the same contents?

    By the way, instead of

    For i = 0 To UBound(arrNames)
    UserForm1.ListBox1.AddItem arrNames(i)
    Next

    you can use the single instruction

    UserForm1.ListBox1.List = arrNames

  8. #8
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: unknown range (Excel 2000)

    I want to populate them from 3 seperate finds of text- listbox1.additem string1
    listbox2.additem string2
    listbox3.additem string3

    does that help?

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: unknown range (Excel 2000)

    If I understand you correctly (I'm not sure), you can encapsulate the code to populate a list box in a separate routine, and call that 3 times. First, create a procedure to fill a list box:

    Private Sub FillListBox(lb As MSForms.ListBox, st As String)
    Dim i As Long
    Dim oCell As Range

    Set oCell = ActiveSheet.Cells.Find(What:=st, LookIn:=xlValues, LookAt:=xlWhole)
    If oCell Is Nothing Then
    MsgBox "Value not found", vbExclamation
    Exit Sub
    End If

    i = 0
    Do Until oCell.Offset(i, 0).Value = ""
    lb.AddItem oCell.Offset(i, 0).Value
    i = i + 1
    Loop
    End Sub

    This procedure has two arguments: the first is the list box to be populated, the second is the string to be searched. In the code that opens the userform, call the procedure once for each list box:

    Private Sub StartForm()
    FillListBox UserForm1.ListBox1, "String1"
    FillListBox UserForm1.ListBox2, "String2"
    FillListBox UserForm1.ListBox3, "String3"
    UserForm1.Show
    End Sub

  10. #10
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: unknown range (Excel 2000)

    Thank you Hans

  11. #11
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: unknown range (Excel 2000)

    As I have read this thread I am looking to see what you wish to accomplish. It occurs to me that you are mainly trying to keep
    your list boxes CURRENT - up to date as users add or delete items in a range.
    If that's the case, here's another approach:

    1. Create a Named Range (String1) but make it DYNAMIC: (Assume String1 is in A1)
    (Insert>Name > RefersTo: =Offset(Sheet1!$A$1,1,0,CountA(Sheet1!$A$:A500),1)
    (The A500 needs to be larger than any anticipated need)
    2. When you set up the userform listbox, manually set the ListFillRange one time only to String1

    Nothing further is needed at run time as long as there are no blanks in the list.
    However, if I understand your loop that creates the arrNames, it too will stop
    adding items when it encounters a blank in the list.

    If the above interests you, I have developed a way to get around the "blank" issue
    that I can share with you
    HTH

Posting Permissions

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