Search function in excel user form

[ijswalker]

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

 

[bk]

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]

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.

 

[Greg Truby]

http://www.mrexcel.com/board2/viewtopic.php?t=184444&highlight=beforeupdate

 

[ijswalker]

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]

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:

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]

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]

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]

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