User Form-error 1004 unless opened in sheet w/combobox data

  • Thread starter Thread starter dpaulos
  • Start date Start date
D

dpaulos

hi there,

one last prob w/my user form: my comboboxes are filled with data fro
sheet(4) also named "data" and work fine as long as sheet(4) is open i
the background. the prob is, that i would like sheet(1) to be open i
the background while i use the form.

i am getting runtime error 1004 method range of object_global faile
when i try to open my user form while in sheet(1).

when i debug, the macro gets stuck in an endless loop with the cod
below.

Private Sub userform_initialize()

With Worksheets("data")

For Each Cell In Worksheets("data").Range("f1:f500")
If Cell.Row <> 1 Then
res = Application.Match(Cell.Value, Range(Range("f1"),Cell.Offset(-1
0)), 0)
If IsError(res) Then
Combobox1.AddItem Cell.Value
End If
Else
Combobox1.AddItem Cell.Value
End If
Next

For Each....

thanks again!
din
 
You have an unqualified range and unqualified ranges will go to the active sheet
and if that activesheet isn't "data", then you've got trouble with a capital T.

With Worksheets("data")
For Each Cell In .Range("f1:f500")
If Cell.Row <> 1 Then
res = Application.Match(Cell.Value, _
.Range(.Range("f1"),Cell.Offset(-1,0)), 0)
If IsError(res) Then
Combobox1.AddItem Cell.Value
End If
Else
Combobox1.AddItem Cell.Value
End If
Next
end with

Those dots in front of .range() mean that it belongs to the previous With
statement (with worksheets("data") in this case.
 
wow. you guys are quick!! Dave, thanks so much for your help, this
form is DONE. i had a feeling it was a range problem.
thanks,
dina
 
Back
Top