Why my UserForm cannot be initialized?

**SMC** · 03-01-2007, 11:54 AM

I have some data sheet ('StateA' in attached example.xls) that feeds my combo boxes.

Horizontal orientation of data blocks in that sheet is not appropriate because of large number of needed inputs (max number of columns is not big enough to hold all needed data blocks).

Therefore i transposed all horizontal data blocks from 'StateA' sheet, to vertical arranged data blocks in 'Needed' sheet (of the same examp. xls) /I did that by copy(all blocks) - paste special - transpose/.
Of course, i named that rearranged sheet with the same name as previous one (original deleted).

Subsequently, i redefined the code that deals with this data sheet, to accept vertically oriented data blocks.

Old version (horiz. data blocks):[vba]Private Sub ArrayFill()
Dim r As Range

Set DataXCollection = New Collection
Set r = Worksheets("SheetX").Range("A1")

Do Until r = ""
'make sure we create a new instance of your custom type on every
iteration
Set dx = New DataX
dx.CodeX = r.Text
dx.PriceX = r.Offset(, 1).Value
dx.ColorX = Application.Transpose(Range(r.Offset(1),

r.Offset(1).End(xlDown)))
dx.SizeX = Application.Transpose(Range(r.Offset(1, 1), r.Offset(1,

1).End(xlDown)))
Product.AddItem dx.CodeX
'add this reference of an instance of your custom type to
'to the DataXCollection collection for future use
'use the product code as the key
DataXCollection.Add dx, dx.CodeX ' dx.CodeX -key)
Set r = r.Offset(, 2)
Loop

Product.Text = "input product"
End Sub[/vba]
New version (accepts rearranged data - with vertical orientation):[vba]Private

Sub ArrayFill()
Dim r As Range

Set DataXCollection = New Collection
Set r = Worksheets("SheetX").Range("A1")

Do Until r = ""
'make sure we create a new instance of your custom type on every
iteration
Set dx = New DataX
dx.CodeX = r.Text
dx.PriceX = r.Offset(1).Value
dx.ColorX = Application.Transpose(Range(r.Offset(, 1), r.Offset(,

1).End(xlRight)))
dx.SizeX = Application.Transpose(Range(r.Offset(1, 1), r.Offset(1,

1).End(xlRight)))
Product.AddItem dx.CodeX
'add this reference of an instance of your custom type to
'to the DataXCollection collection for future use
'use the product code as the key
DataXCollection.Add dx, dx.CodeX ' dx.CodeX -key)
Set r = r.Offset(2)
Loop

Product.Text = "input product"
End Sub[/vba]
But now, when i try to start my UserForm by clicking on its button, i get the following error:

Run-time error '1004':
Application-defined or object-defined error

with[vba]Private Sub CommandButton1_Click()
OrderForm.Show
End Sub [/vba]OrderForm.Show yellow marked ???

How the change (transposition) that i made in the code can cause such a problem ???

PS
[vba]Private Sub UserForm_Initialize()
flag_main = False
Call ArrayFill
End Sub[/vba]

**mvidas** · 03-01-2007, 12:33 PM

Without looking too far into it right now, do you really have "Private" and "Sub ArrayFill()" on two separate lines like your code shows:[vba]Private

Sub ArrayFill()[/vba]If so, either delete the Private line or re-make it "Private ArrayFill()"

If that doesnt solve it, let me know and I can take a closer look at the innards
Matt

**SMC** · 03-01-2007, 01:39 PM

No, it stands in the same line[vba]Private Sub ArrayFill()
Dim r As Range
...
...[/vba]
Thanks a lot

**geekgirlau** · 03-01-2007, 05:11 PM

What references do you have selected?

**SMC** · 03-01-2007, 05:42 PM

I've just solved this problem.
It was .End(xlRight), instead of .End(xlToRight) (btw why not .End(xlToUp) or .End(xlToDown) ????)

But although i can now open my Form, as soon as i click 'Change Product' i get:

Run-time error '9':
Subscript out of range

so there is no end of my torments

[vba]Private Sub SomeProcedure()
...
...
For i = 0 To UBound(dx.SizeX) - 1
ActiveSheet.Cells(korak, 3 + i) = dx.SizeX(i + 1)
Next i
...
[/vba]
dx.SizeX(i + 1) =<Subscript out of range> !!!!!!!

beside the ArrayFill() (above given code), i have "Product_Change() " procedure:
[vba]Private Sub Product_Change() 'Change Event for combo 'Products'

flag_artikal = False

'get the correct DataX object reference from your collection
Set dx = DataXCollection(Product.Text)
PriceInput.Text = dx.PriceX
Color.List = dx.ColorX
Color.ListIndex = 0
Size.List = dx.SizeX
Size.ListIndex = 0
End Sub[/vba] ..which initializes dx objects.

Two procedures are in charge for dx objects: ArrayFill() & Product_Change(), so I think something's between those procedures is wrong.

However i'm wondering why the same code was working with previous transposition (arrangement of data blocks - that is horizontal) of the data Sheet.

What this arrangement has to do with mentioned error ??? and of course HOW to resolve it.

Thanks

**geekgirlau** · 03-01-2007, 05:45 PM

SMC, we really need ALL of your code here ...

**SMC** · 03-01-2007, 05:52 PM

The whole code is not well arranged yet, and it has a lot of unused, obsolet stuff.
Secondly, the most of variables, almost all of comments, and all Form items names are not in english.

Nevertheless, if you think that such a code could be of some help to help me out, i would attach it here.

**SMC** · 03-01-2007, 06:02 PM

Ok, here you go.

Version V1 (with old 'SheetX' /data sheet/ arrangement) is working.
Version V2 (with rearranged 'SheetX' - to vertical data blocks) doesn't work.

Many thanks

**SMC** · 03-01-2007, 06:31 PM

Huh, no views yet.

I badly need the solution in the next 10h.

ps
(to save my skin)

**SMC** · 03-02-2007, 10:08 AM

Well, my skin is on a hanger.

However, could somebody tell me (a million $ Q) how can mentioned slight change in the ArrayFill() procedure (scan orientation) cause that i cannot retrieve any element of dx.SizeX array anymore.

This array is properly populated, what is proved by the MsgBox(UBound(dx.SizeX)):
[vba]...
For i = 0 To (UBound(dx.SizeX) - 1)

MsgBox (UBound(dx.SizeX)) ' !!! output=5 (for certain product)

MsgBox (dx.SizeX(0)) ' !!! Subscript out of range
MsgBox (dx.SizeX(1)) ' !!! Subscript out of range
MsgBox (dx.SizeX(2)) ' !!! Subscript out of range
MsgBox (dx.SizeX(3)) '!!! Subscript out of range
MsgBox (dx.SizeX(4)) '!!! Subscript out of range

ActiveSheet.Cells(korak, 3 + i) = dx.SizeX(i + 1) '!!! Of course 'Subscript out of range'
Next i[/vba]
What is inexplicable is that the same code with horizontal orientation in data sheet (SheetX), and horizontal scanning in the ArrayFill() procedure works without any problem.

I'm afraid even to mention that i think this working code (V1), in fact shouldn't work in my opinion, because of exact these problematic lines:
[vba]For i = 0 To (UBound(dx.SizeX) - 1)
...
ActiveSheet.Cells(korak, 3 + i) = dx.SizeX(i + 1)[/vba] .. as the Option Base has not been set to 1.
Why this doesn't produce 'out of range' error?

So, in my opinion, the code that shouldn't work (V1), is working, but that one which should work(V2), doesn't.

**SMC** · 03-02-2007, 03:40 PM

bump

**SMC** · 03-03-2007, 03:40 AM

**Norie** · 03-03-2007, 06:31 AM

SMC

The form in V2 worked fine for me.

PS Why all the class module stuff?

As far as I can see that's just complicating things.

**SMC** · 03-03-2007, 01:14 PM

Thanks for the reply.

Did you try to put some values in combo boxes?

Set first two boxes and press 'Unesi', subsequently set some value in the 'Grupa' combo, press 'Nova grupa', then if you set a value in the 'Artikal' combo and press 'Novi artikal', you'll get "Run-time error: Subscript out of range".

As for the class module (i'm using just 1 (DataX)), it is suggestion from forum members. I need that class to fill all data from data sheet in the DataXCollection in order to use it later in the code whenever it needs certain data.

**Norie** · 03-03-2007, 01:58 PM

SMC

I thought the problem was when the form initialised?

Not when you actually select values on it.

PS I'm not saying don't use class modules but to me it just seems to complicate things.

I don't see any advantage in using them either.

The lifetime of any 'normal' variables declared in the userform module should last as long as the userform is loaded.

Then again I might be missing something there.

**SMC** · 03-03-2007, 02:17 PM

Yes, that was the problem (before i realized i have a bigger one too), but i solved it by changing xlRight to xlToRight.

I haven't enough knowledge of VBA Classes to explain to you why i actually use it.

**tstom** · 03-04-2007, 03:52 AM

SMC. Horizontal ranges are evaluated differently than vertical ranges when coerced into a variant type. The Transpose function only considers the first element which is fine when working with a vertical range. Remember that any range converted to a variant will be a two dimensional, one-based array. The first element will be in accordance with rows, the second with columns...

Use this code for horizontal ranges...

[vba]
dx.ColorX = FixArray(Range(r.Offset(, 1), r.End(xlToRight)))
dx.SizeX = FixArray(Range(r.Offset(1, 1), r.Offset(1, 1).End(xlToRight)))

Private Function FixArray(MyRange As Range)
Dim a, b, c

a = MyRange
ReDim b(1 To UBound(a, 2))

For c = LBound(a, 2) To UBound(a, 2)
b(c) = a(LBound(a, 1), c)
Next

FixArray = b

End Function
[/vba]

**SMC** · 03-04-2007, 08:14 AM

tstom, what to say?

I have no other comment but

-

I encountered one more issue in 'my' code.

After a number of data records (added Articles), just about a dozen less than it is needed, system gives an error (if i recall it well) "Run-time error '6': Overflow", and opens debug (editor) on some loop.

Because i was in worse problem (and time shortage), i didn't note where exactly in the code it happened.

So, i've "solved" that by copy-paste of some printed "Artikal" block (just as a pattern), and putting data manually in a last few articles.

When i found out the source of the problem i'll come back here with s.o.s.

Thanks a million times, tstom

Thread: Why my UserForm cannot be initialized?

Thread Tools

Display

Why my UserForm cannot be initialized?

Posting Permissions