Urgent - manual input in a combo box

santaclose · #1 (**permalink**) 03-01-07, 05:56

Hi

I need a really quick help.

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

Very thanks in advance.

gvee · #2 (**permalink**) 03-01-07, 06:06

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?

santaclose · #3 (**permalink**) 03-01-07, 06:30

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

gvee · #4 (**permalink**) 03-01-07, 06:53

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.

santaclose · #5 (**permalink**) 03-01-07, 07:39

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.

santaclose · #6 (**permalink**) 03-01-07, 08:02

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.

santaclose · #7 (**permalink**) 03-01-07, 08:41

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:

Quote:

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 ???

gvee · #8 (**permalink**) 03-01-07, 08:47

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

santaclose · #9 (**permalink**) 03-01-07, 10:42

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

Internet Services

Web Development

Digital Marketing

Consumer Tech