Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
unable to protect cells in macro sheet b/c runtime error 1004 | Excel Worksheet Functions | |||
run time error 1004 for other user | Excel Discussion (Misc queries) | |||
Error 1004 using array in Sheet select. | Excel Discussion (Misc queries) | |||
MS JET database engine error: workgroup information file is missing or opened exclusively by another user | Excel Discussion (Misc queries) | |||
User Form Error | Excel Discussion (Misc queries) |