Search function in excel user form

ijswalker

New Member
Joined
Nov 22, 2005
Messages
31
I have a simple user form created in excel and would like to be able to choose Purchase Order Number from a drop down list and have the rest of the text boxes to populate the relevant info that relates to the Purchase Order Number. Can anyone help?

Also, is it possible to set up sub froms in excel?

Thanks

Ian
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

bk

Active Member
Joined
Jun 2, 2002
Messages
387
How about

feeding the PO# that is chosen in your drop-down into a cell. And then, in other cells use a VLOOKUP formula (or whatever formula might be appropriate) to get the relevant info.

Then, just populate the info in those cells back into the text boxes on your UserForm.

This may require a 2-step UserForm. But maybe not, I haven't though through it deeply enough. HTH
 

ijswalker

New Member
Joined
Nov 22, 2005
Messages
31
I was more looking to use VBA to kick off the search so as I didn't have to leave the form I am in.
 

ijswalker

New Member
Joined
Nov 22, 2005
Messages
31

ADVERTISEMENT

I have tried the following code and can't get the other cells to change.

I am trying to get information returned to my userform when I type in the name of a project. As you can see from the code I have about 45 columns to pull info from that relate to a project. Ideally I'd like to be able to use a combo box to select the existing project.


Can someone tell me what I'm doing wrong?


Private Sub PROJECTNAME_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Dim c As Range, ws As Worksheet

Application.ScreenUpdating = False

Set ws = Worksheets("Database")

If PROJECTNAME.Text = "" Then
Exit Sub
End If

Sheets("Database").Activate

For Each c In ws.Range("A1:A1000")
If c.Value = PROJECTNAME.Text Then
c.Activate
DEPARTMENT.Text = Cells(r, 2)
AIRPORT.Text = Cells(r, 3)
CARRYOVER.Text = Cells(r, 4)
PROJSTARTDATE.Text = Cells(r, 5)
PROJNUMBER.Text = Cells(r, 6)
SUBMITTEDBY.Text = Cells(r, 7)
SPONSOR.Text = Cells(r, 8)
DATESUBMITTED.Text = Cells(r, 9)
AIRLINEAPPROVALYEAR.Text = Cells(r, 10)
REGULATORY.Value = Cells(r, 11)
STRATEGICGROWTH.Value = Cells(r, 12)
LIFEEXPECTANCY.Value = Cells(r, 13)
SERVICEQUALITY.Value = Cells(r, 14)
ECONOMICDEVELOPMENT.Value = Cells(r, 15)
INFRADEV.Value = Cells(r, 16)
IMPROVEEFF.Value = Cells(r, 17)
GRANTFUNDING.Value = Cells(r, 18)
BUD2006.Text = Cells(r, 19)
REF2006.Text = Cells(r, 20)
BUDTOTAL.Text = Cells(r, 21)
PRE2007.Text = Cells(r, 22)
BUD2007.Text = Cells(r, 23)
BUD2008.Text = Cells(r, 24)
BUD2009.Text = Cells(r, 25)
BUD2010.Text = Cells(r, 26)
FUTUREYEARS.Text = Cells(r, 27)
YEAROFCOMPLETION.Text = Cells(r, 28)
ASSETLIFE.Text = Cells(r, 29)
FUTUREMAINTCOSTS.Text = Cells(r, 30)
FUTUREOPCOSTS.Text = Cells(r, 31)
ADDITIONALSTAFF.Text = Cells(r, 32)
INCNETREVENUE.Text = Cells(r, 33)
INCEXPENSESAV.Text = Cells(r, 34)
STAFFREDUCTIONS.Text = Cells(r, 35)
PAYPACKPERIOD.Text = Cells(r, 36)
INTERNALRR.Text = Cells(r, 37)
FEDSTATE.Text = Cells(r, 38)
PFC.Text = Cells(r, 39)
CIF.Text = Cells(r, 40)
OTHER.Text = Cells(r, 41)
TOTALFUND.Text = Cells(r, 42)
COSTALLOC.Text = Cells(r, 43)
End If
Next

Sheets("Database").Activate
Application.ScreenUpdating = True

End Sub
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
Ian, if you scroll down that thread a bit, you'll see that I recommend using FIND rather than a (slow) loop-based solution. FWIW, the problem with the code you posted was that you never set r to anything. Reworking your code as little as possible to make use of FIND we end up with:
Code:
Private Sub PROJECTNAME_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

    Dim c As Range, ws As Worksheet
    
    Application.ScreenUpdating = False
    
    If PROJECTNAME.Text = "" Then Exit Sub
    
    Set ws = Worksheets("Database")
    'Sheets("Database").Activate  ' (shouldn't be necessary)
    
    Set c = ws.Range("A1:A1000").Find(PROJECTNAME.Text)
    If c Is Nothing Then
        ' ?? clear boxes??
    Else
        r = c.Row
        DEPARTMENT.Text = Cells(r, 2)
        AIRPORT.Text = Cells(r, 3)
        '...
    End If
End Sub
Note that I changed from the _Exit event handler to the _BeforeUpdate event handler for the reasons cited in that other thread.
 

ijswalker

New Member
Joined
Nov 22, 2005
Messages
31

ADVERTISEMENT

Hi there,

When I choose from my dropdown menu the other fields in the form change just fine only when I click on the next textbox. I tried setting the focus on the next textbox for the update to happen automatically and that works fine until I click off that one and then try save the updated info.

Can anyone help?

Thanks

Ian
 

XLGibbs

Well-known Member
Joined
Feb 25, 2005
Messages
2,446
Ian, I think the problem lies more in your concept of what events need to happen, and what events should trigger certain actions.

If you set focus to another item in the code, you may want to have an action on the prior text boxes LostFocus event....

Using enter, exit, focus events to trigger things is trickier than using a change event. It would seem like you should be able to use the Find method as Greg pointed out to trigger a search and get the row of the Selected item in the combo box using the change event...

Is this related to your Validation of entries at VBAX? Well, perhaps not..

Seemslike Gregs code would do what you need at first glance...
 

ijswalker

New Member
Joined
Nov 22, 2005
Messages
31
Hi There,

No its not related to the VBAX entries. When I run the event it does bring back the row I am looking for. The only difficulty I have is when I click off the textbox that has SetFocus and try to save, I get an error and it goes straight to the setfocus code.

Thanks

Ian
 

Watch MrExcel Video

Forum statistics

Threads
1,118,083
Messages
5,570,124
Members
412,305
Latest member
Mozz
Top