Consulting

Results 1 to 20 of 21

Thread: Why my UserForm cannot be initialized?

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1

    Question Why my UserForm cannot be initialized?

    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]
    Last edited by SMC; 03-01-2007 at 01:45 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •