Solved

Excel 2010 userform

Posted on 2012-03-14
8
374 Views
Last Modified: 2012-03-24
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
0
Comment
Question by:CRHIV
  • 5
  • 3
8 Comments
 
LVL 14

Expert Comment

by:Zack Barresse
Comment Utility
Hi there,

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("INVENTORY")
    Set rCheck = wsCheck.Range("A2", wsCheck.Cells(wsCheck.Rows.Count, 1).End(xlUp))
    Set rFilter = wsCheck.Range("A3", wsCheck.Cells(wsCheck.Rows.Count, 1).End(xlUp))
    sChars = Me.CboProduct.Value
    If sChars = vbNullString Then
        Me.CboProduct.Clear
        For Each cProd In wsCheck.Range("A3", wsCheck.Cells(wsCheck.Rows.Count, 1).End(xlUp))
            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(xlCellTypeVisible)
        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 As Integer, CloseMode As Integer)
    ThisWorkbook.Worksheets("INVENTORY").AutoFilterMode = False
End Sub


HTH

Regards,
Zack Barresse
0
 

Author Comment

by:CRHIV
Comment Utility
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.
0
 

Author Comment

by:CRHIV
Comment Utility
Attached is the latest FG.xls with changes you suggested. See if I put them in right.
FG.xls
0
 

Author Comment

by:CRHIV
Comment Utility
Thanks,

Chuck
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 14

Accepted Solution

by:
Zack Barresse earned 500 total points
Comment Utility
Hi Chuck,

Let me try to answer your questions.

When the "Inv Adj" is opened, can the cursor be positioned in the "PRODUCT" field?
Yes.  In the Userform_Initialize() routine, before the "End Sub", add this line of code...
Me.CboProduct.SetFocus

Open in new window


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?
AFAIK we don't have this type of functionality or latitude.
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.
A workaround for that would be to use a ListBox to show the data and have the textbox instead of a combobox where the user can type the data.

Attached is the latest FG.xls with changes you suggested. See if I put them in right.
The only thing I didn't see in the code was the UserForm_QueryClose event, which was toward the end of my post.  You should really have that as you don't want your data on the INVENTORY sheet to stay filtered if someone closes the form, and that code takes off the autofilter.

HTH

Regards,
Zack Barresse
0
 

Author Closing Comment

by:CRHIV
Comment Utility
Said what I wanted to do could not be done.
0
 
LVL 14

Expert Comment

by:Zack Barresse
Comment Utility
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
0
 

Author Comment

by:CRHIV
Comment Utility
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.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now