Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2005
    Posts
    11

    Exclamation Unanswered: Urgent - manual input in a combo box

    Hi

    I need a really quick help.

    How to enable manual data input (beside a predefined list) in combo box.

    Very thanks in advance.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Possible solution - add an item to the drop down for "other" and when this is selected active a control for user input.
    User saves this input and then you must add it to the list.

    Any good?
    George
    Home | Blog

  3. #3
    Join Date
    Sep 2005
    Posts
    11
    Quote Originally Posted by georgev
    Possible solution - add an item to the drop down for "other" and when this is selected active a control for user input.
    User saves this input and then you must add it to the list.

    Any good?
    Huh, don't understand.
    How to add an item for "other"?

    I have this code for the Combo item change:
    Code:
    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)   'Product.Text) key(index) 
        PriceInput.Text = dx.PriceX
        Color.List = dx.ColorX
        Color.ListIndex = 0
        Size.List = dx.SizeX
        Size.ListIndex = 0
    End Sub
    Actually the data input is not disabled, but as soon as i manually input some character in the combo (which is not recognized as the first character of some list item) i get (with the "Set dx ..." line yellow marked):

    Run-time error '5':
    Invalid procedure call or argument

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    If <fieldyouentereddatainto>.Text <> DataXCollection(Product.Text) Then
    ...
    Set dx = <fieldyouentereddatainto>.Text
    ...
    Else
    ...
    <as normal>
    ...
    End If
    This is just a stab in the dark - might be worth a try.
    George
    Home | Blog

  5. #5
    Join Date
    Sep 2005
    Posts
    11
    Thanks georgev.

    Huh, i found the error, but now i need help to fix it.

    This code fills certain important array:
    Code:
    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
    I didn't expect so many order items, so i made a Sheet for data storage (that feeds my combo), with data blocks ordered horizontally ('StateA' Sheet in attached file).

    So that, when i arrive to the end of possible number of columns, and therefore try to add an extra data manually in my combo, " ArrayFill() " procedure cannot fill DataXcollection collection (with dx objects), thus manually added data cannot be recognized.

    Well, i need a twofold help here.

    1) A code which will transpose my horizontaly oriented data blocks into verticaly oriented ones ('Needed' Sheet), because it takes an unacceptable amount of time to do it manually.

    2) A redefined code for the (above-mentioned) "ArrayFill()" procedure to deal with verticaly ordered data blocks.


    This help would save my life.
    Attached Files Attached Files

  6. #6
    Join Date
    Sep 2005
    Posts
    11
    I've found a way to solve first issue, with copyall and paste special with transpose option.

    I'll try to quick redefine mentioned code to deal with a such data sheet, but any quicker help would be greately appreciated.

  7. #7
    Join Date
    Sep 2005
    Posts
    11
    I changed the code for ArrayFill() proc.:
    Code:
    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 = "Product input"
    End Sub
    When i click on a button to start program:
    Run-time error '1004':
    Application-defined or object-defined error
    With
    Code:
    Private Sub CommandButton1_Click()
        OrderForm.Show
    End Sub
    OrderForm.Show yellow marked ???

    And i cannot open my Form at all ???

    What OrderForm.Show has to do with it ???
    Last edited by santaclose; 03-01-07 at 09:47.

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    The form cannot be shown if it is not already open - take a look at replacing that line with a doCmd.OpenForm command.

    EDIT: Forgot that this was in the Excel forum, not Access - so my answer is not applicable. Sorry
    George
    Home | Blog

  9. #9
    Join Date
    Sep 2005
    Posts
    11
    Maybe the culprit is here:
    Code:
    Private Sub UserForm_Initialize() 
        flag_main = False 
        Call ArrayFill '! ! ! ! ! ! ! !
    End Sub
    Something's still wrong with "ArrayFill" procedure ?
    ( i have just changed position of the data, nothing else) !!??

    Thanks

Posting Permissions

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