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):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
New version (accepts rearranged data - with vertical orientation):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
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 withPrivate Sub CommandButton1_Click()
OrderForm.Show
End Sub OrderForm.Show yellow marked ???
How the change (transposition) that i made in the code can cause such a problem ???
PS
Private Sub UserForm_Initialize()
flag_main = False
Call ArrayFill
End Sub
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):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
New version (accepts rearranged data - with vertical orientation):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
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 withPrivate Sub CommandButton1_Click()
OrderForm.Show
End Sub OrderForm.Show yellow marked ???
How the change (transposition) that i made in the code can cause such a problem ???
PS
Private Sub UserForm_Initialize()
flag_main = False
Call ArrayFill
End Sub