Aargh! VBA won't load a form - no apparent reason!!!

[cdhoughton]

For some inexplicable reason I have a form-loading problem.

I have a button on a main menu that is intending to unload the main menu and open this form - it works for all my other buttons, but won't work here!!!!!

My code is:
---------------
Private Sub CommandButton12_Click()
Unload Me
frmPrintYrSetting.Show

End Sub
-------------------


It gives a '91' run time error: Object Variable or With block Variable not set, and highlights the frmPrintYrSetting.Show line as being the problem.

I can't see why, can anyone offer help?!?!

 

[Tom Urtis]

Take a close look at the Initialization procedure of the frmPrintYrSetting, or the RowSource properties of ListBox or ComboBox controls in that userform. It might be that something about how they are instructed to load programmatically is not possible (such as a sheet or range name that doesn't exist), causing the error.

 

[cdhoughton]

Is there something wrong with this?

The sheets, ranges and combobox are all correctly named, but have I put some invalid code in?

I'm trying to set some items for my combo box for a certain range, and to include them in the combo box if they <> 0.

If this is incorrect, could you provide an alternative suggestion?

Cheers for any help offered!

-----------------------------------------
Private Sub UserForm_Initialize()
Dim z As Integer
Dim Rng As Range

z = 0
Rng = Sheets("INPUT").Range("E84")

For z = 0 To 9
If Rng.Offset(z, 0).Value <> 0 Then
ComboBox1.AddItem (Rng.Offset(z, 0).Value)
End If
Next z
End Sub
------------------------------------------------

 

[Joe4]

I don't know if this will solve your problem, but I believe when you are setting a range variable, you need to use the word "Set", i.e. I believe:

Rng = Sheets("INPUT").Range("E84")

should be

Set Rng = Sheets("INPUT").Range("E84")

 

[herilane]

This line:
Rng = Sheets("INPUT").Range("E84")
needs to be preceded by Set:
Set Rng = Sheets("INPUT").Range("E84")

 

[cdhoughton]

Cheers, thanks very much for that, it was driving me crazy!