Showing Forms

[j_Southern]

Dear Forum Members
RomperStomper has just helped me by solving a problem "RemoveDuplicates in VBA" which seemed to revolve round a parameter array used in the RemoveDuplicate coding.
On the same workbook I now have a problem where I cannot show a userform because of the following error message
"Could not set list property. Invalid property array index".
The code involved is

Private Sub UserForm_Initialize()
With EntryForm
 .ListBox1.List = Sheets("ALL_UNCHECKED").Range("A11").CurrentRegion.Value
'.ListBox1.List = Sheets("ALL_UNCHECKED").Range("A11:J16")
.TextBox12.Value = Format(Now, "dd/mm/yyyy")
.TextBox13.Value = Sheets("UTILITIES").Range("A10").Value
End With
flag = 0

End Sub

Neither the third line or the rem'd line work, and I am surprised I have used very similar code before. Is it possible that my two problems are connected?
I have attached the full workbook.
OpenForm just enables secondform and it is the button Enter Orders which starts the problem.
The sub userForm_initialise() is in the code of EntryForm

[romperstomper]

I don't get an error with that.

[j_Southern]

RomperStomper
I don't think you had any problem with "RemoveDuplicates either. Is it possible I have some problem with Excel ? Are my two problems linked in any way?
I have got round the coding problem by manualy giving the Listbox a row source of the relevant range.
But should I perhaps reload MS Office ?
John

[romperstomper]

I did have the error with RemoveDuplicates in your file. I do not have the error you describe here with your file - it simply loads the form.

[snb]

I'd reduce the code to:

Private Sub UserForm_Initialize()
 ListBox1.List = Sheets("ALL_UNCHECKED").Range("A11").CurrentRegion.Value
 TextBox12.Text = Format(date, "dd/mm/yyyy")
 TextBox13.Text = Sheets("UTILITIES").Range("A10").Value
End Sub

check the existence of
- Sheets("ALL_UNCHECKED")
- Sheets("UTILITIES")

Since this code is part of the userform's codemodule there's no need to refer to the userform if you are referring a control like 'Textbox12' or 'listbox1'.

[j_Southern]

RomperStomper
I understand, It is still happening to me on exactly the same file. I'll have to put it to the back of my head. thanks for your interest anyway
John

[j_Southern]

snb,
The sheets both certainly exist (check attachment).
I have simplified as you suggest with no change.
I remember it was you that showed me the .List(.ListIndex, x) method and I have used it successfully several times since. It is frustrating that the code works for RomperStomper but not me!
John

[snb]

I can't see a problem. See the attachment.

[j_Southern]

snb,
__LABELSTATSsnb.xlsm opens perfectly , yet I cannot see any important difference in the code!
Any way thanks for your help, I will use your version as mine still does not work.
John