Fault 1004 range of object aplication failed on userform.show

[DB73]

Hey guys,

another problem to solve for me
i have a sheet with some buttons, 1 is for showing my userform (MyForm)

Code to show userform;

Private Sub CommandButton1_Click()
MyForm.Show
End Sub

this 1 is on sheet18(gegevens invoer)

This 1 is on the userform;
Code to load the cmbxs;

Private Sub UserForm_Initialize()

        ComboBox1.List = Application.Range("werkdag_ziek_verlof").value
        ComboBox2.List = Application.Range("projecten").value
        ComboBox3.List = Application.Range("adressen").value
        ComboBox4.List = Application.Range("normale_uren_over_uren").value
        'ComboBox5.List = Application.Range("uur_tarief_percentage").value
        'cbx5
            Dim cell As Range
             For Each cell In Application.Range("uur_tarief_percentage").Cells
                ComboBox5.AddItem cell.Text
                    Next cell
        ComboBox6.List = Application.Range("werktijden").value
        ComboBox7.List = Application.Range("werktijden").value
        ComboBox8.List = Application.Range("pauze_geen_pauze").value
        ComboBox9.List = Application.Range("BTW_verlegd").value
        'ComboBox10.List = Application.Range("BTW_heffing").value
        'cbx10
            For Each cell In Application.Range("btw_heffing").Cells
                ComboBox10.AddItem cell.Text
                    Next cell
        'ComboBox11.List = Application.Range("adressen[klantnaam]").value
        'ComboBox11
            With ComboBox11
              .ColumnCount = 5
                .List = Range("adressen").value
                  End With
        ComboBox12.List = Application.Range("adressen[klantnaam]").value
        ComboBox13.List = Application.Range("enkel_retour_rit").value
        ComboBox14.List = Application.Range("woon_werk_zakelijk").value
        ComboBox15.List = Application.Range("te_declareren_per_km").value
        ComboBox16.List = Application.Range("overige_declaratie").value
        ComboBox17.List = Application.Range("reden_rit").value
        ComboBox18.List = Application.Range("te_declareren_per_km").value
        ComboBox19.List = Application.Range("vervoer").value
        ComboBox20.List = Application.Range("reden_overige_declaratie").value
End Sub

on my search on the WWW i found that it could be a UserForm_Initialize() that causes the problem.
so i skipped items on the UserForm_Initialize() part to look which line causes the problem, but i cant figur out which one.
if i remove the whole code it seems to work and the userform pops up but, without the comboboxes population

any help on this one will be apreciate

 

[davesexcel]

Hard to tell, did you step through the initialize code line by line until the error occurred? or is that what you meant by "skipped items"?

 

[CSmith]

try this line : ComboBox12.List = Application.Range("adressen[klantnaam]").value

 

[CSmith]

Hard to tell most look to be named ranges. Try accessing them using this format instead:

ComboBox##.RowSource = Sheets("SheetName").ListObjects("NamedRange").DataBodyRange.Address(External:=true)

 

[DB73]

try this line : ComboBox12.List = Application.Range("adressen[klantnaam]").value

that 1 isnt right, but this 1 does the job;

ComboBox12.List = Application.Range("adressen").value

but i changed it to;

        With ComboBox12
              .ColumnCount = 5
                .List = Range("adressen").value
                  End With

cbx12 is the same as cbx11

i think i forgot due to many changes last update in my file...
i'm still learning

 

[DB73]

Hard to tell, did you step through the initialize code line by line until the error occurred? or is that what you meant by "skipped items"?

i just removed some lines an tested if it works...afte removing the whole code the userform popt up on the cmdbutton.

but the problem is solved...thanks for the help anyway....

 

[CSmith]

that 1 isnt right, but this 1 does the job;

I was saying the error looked to be at this line.

 

[Fluff]

In the VB Editor goto Tools, Options, General, & check the "Break in class module"
That way when it fails you can click the debug button & it will takes you to the problem.

 

[CSmith]

that 1 isnt right, but this 1 does the job;

ComboBox12.List = Application.Range("adressen").value

but i changed it to;

        With ComboBox12
              .ColumnCount = 5
                .List = Range("adressen").value
                  End With

cbx12 is the same as cbx11

i think i forgot due to many changes last update in my file...
i'm still learning

All good! We all began learning somewhere!

 

[DB73]

the problem for me is that if theres a fail i only can find the yellow marked lines, for this one it gaves a yellow marker on the "userform.show"
so, i could find anything else