Urgent - manual combo input

[SMC]

Hi

I need areally quick help.

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

Very thanks in advance

[Bob Phillips]

That is a combobox's default behaviour. It can only be changed if yoiu change the Style property to fmStyleDropDownList.

[SMC]

That is a combobox's default behaviour. It can only be changed if yoiu change the Style property to fmStyleDropDownList.

In ComboBox properties i have no such an option to change ??

[SMC]

I have this code for the Combo item change:[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) ' Product.Text is a key(index)
PriceInput.Text = dx.PriceX
Color.List = dx.ColorX
Color.ListIndex = 0
Size.List = dx.SizeX
Size.ListIndex = 0
End Sub[/vba]
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

[moa]

Product_Change should be Product_AfterUpdate maybe?

[moa]

Product_Change will execute after each character is entered into the combobox. AfterUpdate executes when the combobox loses focus through pressing the enter or tab key or clicking on another control so you are able to type in the whole string before it fires.

[moa]

Also check out MatchEntry and MatchRequired properties of comboboxes.

[SMC]

Thanks Glen.

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

This code fills certain important collection:
[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]

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()" /actually "Product_Change()" above/ procedure cannot fill DataXcollection collection (with dx objects), thus manually added data cannot be recognized.

Well, i need here a twofold help.

1) A code which will transpose my horizontaly oriented data blocks into verticaly oriented ones ('Needed' Sheet - attached xls), 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.


I know i'm asking much, but this help would save my life.

[SMC]

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.

[SMC]

I changed the code for ArrayFill() proc.:
[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 = "Product input"
End Sub[/vba] When i click on a button to start program:

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

What OrderForm.Show has to do with it ???

[SMC]

Maybe the culprit is here:
[vba]Private Sub UserForm_Initialize()
flag_main = False
Call ArrayFill '! ! ! ! ! ! ! !
End Sub[/vba]
Something's still wrong with "ArrayFill" procedure ?
( i've only changed position of the data, nothing else)

Thanks