How Do I.......

[ElectricSkywalker]

How do I add a lookup into a User Form???
(kinda like a vlookup)

I have created a User Form

In it, (this is a simplified version), I have four fields.

The first is a Text box, txtDate

The second is a Label, lbSales
The third is a Text Box, txtActualSales
The fourth is a Text Box, txtBudgetSales

I want the user to enter in the DATE and the ACTUAL SALES.....but I want the BUDGET SALES to automatically lookup a table on another worksheet....and (based on the Date) automatically display the BUDGET DETAILS for the Date specified.

I have no idea how to go about this??? Can anyone point me in the right direction please.


Thank You

 

[Russell Hauf]

Hard to tell without knowing what format your data is on your worksheet (the data you want to look up). But are you familiar with Application.WorksheetFunction? You could use VLOOKUP if your data is arranged appropriately...

For example,

txtBudgetSales = Application.WorksheetFunction.Vlookup(txtDate.Text, Worksheets("Sheet3").Range("A1:C100"),3,False)

HTH - if not, give an example of your data.

-rh
This message was edited by Russell Hauf on 2002-10-31 17:59

 

[ElectricSkywalker]

Beautiful Russell.....that is exactly what I have been looking for over the past week :0)
I really love how this forum works.


I guess I am not all that familiar with the Worksheet Function.

So is that what WorksheetFunction is used for....when ever you want to add an excel formulas into VBA....you enter "WorksheetFunction" first?

I have one other question with what you provided though....I have tested my code and it works sweet as. What I have used is:

Private Sub txtCONTBUDG1_Enter()
txtCONTBUDG1 = Application.WorksheetFunction.vlookup(lbSunday.Value, Worksheets("Budget").Range("a1:c20"), 3, False)

End Sub


My Question is.....how do I make the budget data automatically come up....with out having to click on the text box.....as in....as soon as the date is specified, the Budget Data comes up.

Thank You
Greg

 

[Russell Hauf]

If your date is being input into the txtDate text box, then just put that same line of code into the AfterUpdate event of txtDate (instead of in the Enter event of the text box you want the value written to).

HTH,

Russell
This message was edited by Russell Hauf on 2002-10-31 19:05