User forms

Posted by CHRIS A on February 01, 2001 4:18 AM

im trying to create a user form for excell 97, to insert entries into a sales data base. the user form is therefore a order form.
How do i get it to return values to cells?
look up values from data already looked up?
this is for my A level course work and no1 at college seems to know any VB!
Many thanks
Chris

Posted by Steve on February 01, 2001 9:09 AM

I hope the following is of some help to you.

You will first need to call the textbox something. i.e. txtTotalSales you will then need to insert this small sub routine into the module.

Public Sub ShowUserForm()
ShowValue
YourUserForm.Show
End Sub

The above will call the sub routine which will ask for information from the spreadsheet to be displayed in the text box(s).

The sub routine which calls for information from a spreadsheet is:

Public Sub ShowValue()
YourUserForm.txtTotalSales = Worksheets("YourWorkSheetName").Range("J56").Value
End Sub

Basically to display information from the User Form to the spreadsheet simply reverse the statement to read.

Worksheets("YourWorkSheetName").Range("A1").Value = YourUserForm.txtTotalSales

You will need to replace things like YourSheetName and YourUserForm with actual names from your spreadsheet.

Hopes its of use.

Steve Mark

Posted by CHRIS A on February 01, 2001 10:38 AM

CHEERS MATE

I think it will be thanks for that, ive spent hours searching the web for such a solution. Ta