CRHIV
asked on
Excel 2010 userform
Any sheet 1-20 on the attached program is an order taking form. To the right of the sheet is a button for "Inventory Adjustment". This userform has a drop down box for "PRODUCT". Right now you have to scroll down to pick the item needed. How would I change the code, so that the item number could be typed in, and the program would help you find the item in the data base? For instance, if an "A" was typed, the window would show items that start with "A". If the next letter was "AA" the window would show items starting with "AA", and so on. But would not let you type an item was not in the data base.
FG.xls
FG.xls
ASKER
Thanks for the code. Can it be altered a bit? When the "Inv Adj" is opened, can the cursor be positioned in the "PRODUCT" field? When a letter is entered, can the drop down box appear with a field of 8 items and the descriptions listed starting with the item of the closest match for the first letter? When you leave the "PRODUCT" field, it needs to pull the other information off of the "INVENTORY" sheet to fill in "BATCH NO", "LOCATION", "CURRENT" count of inventory. The way it is now, if you start typing, there is no look up help. The only way to get look up help is to first open the drop down box. Then even if the item is selected with the mouse, it doesn't populate the other data when you leave the "PRODUCT" field. The only way to get it to populate the other fields is to do it with the original drop down box.
ASKER
Attached is the latest FG.xls with changes you suggested. See if I put them in right.
FG.xls
FG.xls
ASKER
Thanks,
Chuck
Chuck
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Said what I wanted to do could not be done.
Huh. Not that I mind about the points awarded, but some people here *really* do care. Awarding an "Average" answer because of the software limitations is, well, naive. I'm more than happy to tell you things you can't do in Excel all day long, but it certainly shouldn't reflect on the poster. For future reference, since it can be a big deal to people, I would highly recommend you base your point awards based on merit, not what you deem is appropriate based on application limitations in reference to the poster. Kind of unfair to the posters. :)
Have a good one,
Zack
Have a good one,
Zack
ASKER
Sorry. I felt, with my limited programming knowledge, that it could be done. I didn't know how to go about it. Every seemingly impossible question I had ever asked had been answered to a workable solution. Thanks for your help.
You can use something like this in your userform code module...
Private Sub CboProduct_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Dim wsCheck As Worksheet
Dim rCheck As Range
Dim rFilter As Range
Dim cProd As Range
Dim sChars As String
Set wsCheck = ThisWorkbook.Worksheets("I
Set rCheck = wsCheck.Range("A2", wsCheck.Cells(wsCheck.Rows
Set rFilter = wsCheck.Range("A3", wsCheck.Cells(wsCheck.Rows
sChars = Me.CboProduct.Value
If sChars = vbNullString Then
Me.CboProduct.Clear
For Each cProd In wsCheck.Range("A3", wsCheck.Cells(wsCheck.Rows
With Me.CboProduct
.AddItem cProd.Value
.List(.ListCount - 1, 1) = cProd.Offset(0, 3).Value
End With
Next cProd
Exit Sub
End If
If KeyCode < 33 Or KeyCode > 126 Then Exit Sub
rCheck.AutoFilter 1, sChars & "*"
On Error Resume Next
Me.CboProduct.Clear
For Each cProd In rFilter.SpecialCells(xlCel
With Me.CboProduct
.AddItem cProd.Value
.List(.ListCount - 1, 1) = cProd.Offset(0, 3).Value
End With
Next cProd
wsCheck.AutoFilterMode = False
End Sub
Basically it's using AutoFilter to filter for the characters you entered into the combobox and 1) clearing the combobox, 2) re-populating the combobox with the filtered values. You should also add this line to a userform close routine (I didn't see one in your file, so just add it to your userform code module)...
Private Sub UserForm_QueryClose(Cancel
ThisWorkbook.Worksheets("I
End Sub
HTH
Regards,
Zack Barresse