Can I create a form that display data from 2 different sheets?

[mimitan]

Greeting to All,
I have a Item sheet with a list of data like this
Item progid StartDate EndDate .....
------------------------------------------------------
11223 AA 01/25/05 03/25/05
24555 AB 02/01/05 05/15/05
...etcetera
My goal is to create a "Form" that consists of 4 boxes that display the data (Item, progid, startdate, enddae) from the Item sheet and then 4 or 5 others boxes that allow users to ordering info such as Quantity, delivery date, store# (and possible a Combo box with a list of store), Person Name, Company. And at last A command button to click on when the order is complete so that it put the data into another sheet call Order.
This form basically navigate through the Item Sheet and when user see an item they want to order, they would fill the form and click on Command button that put the info into Order sheet, and when they naviagte back to the item they ordered, all boxes should be filled with the data.Make sense?
How can I create such "Form"
Thanks in advance for you time.

 

[VBslammer]

Here's an example of how to load the form's values and post the user input back to different sheets. This is very basic but shows how to reference the sheet names and cells:

Private Sub cmdUpdate_Click()

  'check required textbox values before updating
  If txtOrderID.Value <> "" And txtOrderQty.Value <> "" Then
    
    'valid values, update cells
    Sheets("Orders").Range("A1").Value = txtOrderID.Value
    Sheets("OrderQuantities").Range("A1").Value = txtOrderQty.Value
    
    'clear the form
    txtOrderID.Value = ""
    txtOrderQty.Value = ""
    
    'announce success
    MsgBox "Order complete"
    
    'close form (optional)
    Unload Me
    
  Else
    
    'invalid entries
    MsgBox "Please fill in all required fields."
    
  End If
End Sub

Private Sub UserForm_Initialize()

  'use labels for static values
  'and fill them on form load
  lblOrderDate.Caption = Sheets("OrderDates").Range("D5").Value
  
End Sub

If you set the label's special effect to 2 (sunken) and change the backcolor to window background it will look just like a text box but it won't accept focus. You could just as easily use textboxes and set the Locked property to true and TabStop property to false if the user has a need to select & copy the values for some reason.

VBSlammer

Unemployed in Houston, Texas

 

[mimitan]

Thanks VBSlammer.
I just started to learn VBA-Excel. Could you help me by guiding me where and how I should place these codes?
Again Thank you for your response and time

 

[VBslammer]

I think I mis-read your explanation. When you said "Form" I instinctively thought of a "UserForm" which is actually a window or container for textboxes, buttons or other controls. Now that I look at your post again I think you were referring to a worksheet to be used as a "Form" for people to fill out. Is that right?



VBSlammer

Unemployed in Houston, Texas

 

[mimitan]

Thanks for getting back with me VBslammer.

I think a "UserForm" would work too. Which one does it fit better in my situation?
I have though over and plan to change the implementation a little. Please allow to explain the change which I though would be easier. I still have this table
Item progid StartDate EndDate .....
------------------------------------------------------
11223 AA 01/25/05 03/25/05
24555 AB 02/01/05 05/15/05
445566 AC 02/01/05 05/15/05
125856 BB 01/26/05 03/30/05
and so on
and on this Item sheet I would like to have a small "Form" or "UserForm" (which is always on the top of Item sheet) so that when I scroll down the Item sheet and find an Item I would like to order I would enter info such as Item, Quantity, delivery date, store# (and possible a Combo box with a list of store),Company on that "form" or "UserForm" and when the info fill the form (all filled) the info will be send to the Order sheet when the ENTER key is pressed and the "UserForm" will clear itself out for the next order.
This is what I hope to accomplish but not sure how. I appreciate your help.

 

[VBslammer]

I've built workbooks in the past that used UserForms to interact with worksheets, so it can be done. Before Access 2000, we had to use the API to change the UserForm's Modal state so we could actually interact with the worksheet, but now you can just set the UserForms's ShowModal property to False in the property sheet.

If you haven't worked with UserForms before your best bet is to look at a working example to get some ideas. Try downloading the demo called "UserForm Example" from the link below to see how to do some stuff:

http://www.mfdabbs.pwp.blueyonder.co.uk/Excel_Pages/Useful_VBA_Code_Examples.html

I'm sure if you did some googling you could come up with even better examples.

VBSlammer

Unemployed in Houston, Texas

 

[mimitan]

Thanks VBSlammer
Merry Xmas!