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

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

--
Message posted from http://www.ExcelForum.com

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

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.



"dpaulos <" wrote:

hi there,

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

i am getting runtime error 1004 method range of object_global failed
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 code
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!
dina

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson

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

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


---
Message posted from http://www.ExcelForum.com/