2 comboboxs, different sheet references

[Kryer]

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

[Jim Cone]

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" <[email protected]>
wrote in message
news:[email protected]...
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

[Kryer]

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" <[email protected]>
> wrote in message
> news:[email protected]...
> 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
>