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("D25"). 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
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("D25"). 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