+ Reply to Thread
Results 1 to 3 of 3

2 comboboxs, different sheet references

  1. #1
    Kryer
    Guest

    2 comboboxs, different sheet references

    I have a userform that has 2 comboboxs, I want combobox1 to populate from
    Sheet1, range("A2:A100") and i want combobox2 to populate from sheet2,
    range("D2:D5"). But when I do that it wont populate the combox values if I am
    on sheet3. Then if i am on sheet2 only combobox2 values appear and etc.. what
    is wrong with my code here:

    Private Sub UserForm_Initialize()
    Dim sh As Worksheet
    Dim rng As Range
    Dim MyArray() As Variant
    Dim sh1 As Worksheet
    Dim rng1 As Range
    Dim MyArray1() As Variant

    Set sh = Workbooks("Medical Bills.xls").Worksheets("DocInfo")
    Set rng = Range("A2:A100")
    MyArray = rng
    ComboBox1.List = MyArray

    Set sh1 = Workbooks("Medical Bills.xls").Worksheets("Titles")
    Set rng1 = Range("B1:B5")
    MyArray1 = rng1
    ComboBox2.List = MyArray1
    End Sub

    Please help

  2. #2
    Jim Cone
    Guest

    Re: 2 comboboxs, different sheet references

    Kryer,

    The rng and rng1 objects were not qualified by the sheet objects
    and were therefore referencing the active sheet.
    I changed the MyArray variable declarations from an array to variant.
    by removing the "()". The code should now work in xl97.
    In xl97, you cannot assign to an array.
    A ListIndex of zero was assigned to each combobox, so the
    first value in each list will be displayed. (keep, change, or remove)

    Jim Cone
    San Francisco, USA
    '------------------------------

    Private Sub UserForm_Initialize()
    Dim sh As Worksheet
    Dim sh1 As Worksheet
    Dim rng As Range
    Dim rng1 As Range
    Dim MyArray As Variant
    Dim MyArray1 As Variant

    Set sh = Workbooks("Medical Bills.xls").Worksheets("DocInfo")
    Set rng = sh.Range("A2:A100")
    MyArray = rng.Value
    ComboBox1.List = MyArray
    ComboBox1.ListIndex = 0
    Set sh1 = Workbooks("Medical Bills.xls").Worksheets("Titles")
    Set rng1 = sh1.Range("B1:B5")
    MyArray1 = rng1.Value
    ComboBox2.List = MyArray1
    ComboBox2.ListIndex = 0

    Set rng1 = Nothing
    Set rng = Nothing
    Set sh1 = Nothing
    Set sh = Nothing
    End Sub
    '-------------------------


    "Kryer" <Kryer@discussions.microsoft.com>
    wrote in message
    news:ACAED389-7A50-4E5B-A27F-D4D38A227963@microsoft.com...
    I have a userform that has 2 comboboxs, I want combobox1 to populate from
    Sheet1, range("A2:A100") and i want combobox2 to populate from sheet2,
    range("D2:D5"). But when I do that it wont populate the combox values if I am
    on sheet3. Then if i am on sheet2 only combobox2 values appear and etc.. what
    is wrong with my code here:
    Private Sub UserForm_Initialize()
    Dim sh As Worksheet
    Dim rng As Range
    Dim MyArray As Variant
    Dim sh1 As Worksheet
    Dim rng1 As Range
    Dim MyArray1 As Variant
    Set sh = Workbooks("Medical Bills.xls").Worksheets("DocInfo")
    Set rng = Range("A2:A100")
    MyArray = rng
    ComboBox1.List = MyArray
    Set sh1 = Workbooks("Medical Bills.xls").Worksheets("Titles")
    Set rng1 = Range("B1:B5")
    MyArray1 = rng1
    ComboBox2.List = MyArray1
    End Sub
    Please help

  3. #3
    Kryer
    Guest

    Re: 2 comboboxs, different sheet references

    Thanks for the help

    "Jim Cone" wrote:

    > Kryer,
    >
    > The rng and rng1 objects were not qualified by the sheet objects
    > and were therefore referencing the active sheet.
    > I changed the MyArray variable declarations from an array to variant.
    > by removing the "()". The code should now work in xl97.
    > In xl97, you cannot assign to an array.
    > A ListIndex of zero was assigned to each combobox, so the
    > first value in each list will be displayed. (keep, change, or remove)
    >
    > Jim Cone
    > San Francisco, USA
    > '------------------------------
    >
    > Private Sub UserForm_Initialize()
    > Dim sh As Worksheet
    > Dim sh1 As Worksheet
    > Dim rng As Range
    > Dim rng1 As Range
    > Dim MyArray As Variant
    > Dim MyArray1 As Variant
    >
    > Set sh = Workbooks("Medical Bills.xls").Worksheets("DocInfo")
    > Set rng = sh.Range("A2:A100")
    > MyArray = rng.Value
    > ComboBox1.List = MyArray
    > ComboBox1.ListIndex = 0
    > Set sh1 = Workbooks("Medical Bills.xls").Worksheets("Titles")
    > Set rng1 = sh1.Range("B1:B5")
    > MyArray1 = rng1.Value
    > ComboBox2.List = MyArray1
    > ComboBox2.ListIndex = 0
    >
    > Set rng1 = Nothing
    > Set rng = Nothing
    > Set sh1 = Nothing
    > Set sh = Nothing
    > End Sub
    > '-------------------------
    >
    >
    > "Kryer" <Kryer@discussions.microsoft.com>
    > wrote in message
    > news:ACAED389-7A50-4E5B-A27F-D4D38A227963@microsoft.com...
    > I have a userform that has 2 comboboxs, I want combobox1 to populate from
    > Sheet1, range("A2:A100") and i want combobox2 to populate from sheet2,
    > range("D2:D5"). But when I do that it wont populate the combox values if I am
    > on sheet3. Then if i am on sheet2 only combobox2 values appear and etc.. what
    > is wrong with my code here:
    > Private Sub UserForm_Initialize()
    > Dim sh As Worksheet
    > Dim rng As Range
    > Dim MyArray As Variant
    > Dim sh1 As Worksheet
    > Dim rng1 As Range
    > Dim MyArray1 As Variant
    > Set sh = Workbooks("Medical Bills.xls").Worksheets("DocInfo")
    > Set rng = Range("A2:A100")
    > MyArray = rng
    > ComboBox1.List = MyArray
    > Set sh1 = Workbooks("Medical Bills.xls").Worksheets("Titles")
    > Set rng1 = Range("B1:B5")
    > MyArray1 = rng1
    > ComboBox2.List = MyArray1
    > End Sub
    > Please help
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1