MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Price listing


Posted by Richard on July 11, 2001 8:50 AM

How can i create a user form, unalterable to call up prices charged to customers.

If possible first select customer, this will then bring up two lists..items previously ordered and general items.

When this item is selected either previous price charged or list price will appear!

Thanks

Richard


Posted by Richard on July 11, 2001 3:00 PM

Richard,

What you're asking for is possible. First create the userform in the VBA editor. Create a Combobox, and as its Rowsource use the worksheet range where the customers' names are stored.

Create two more another Comboboxes for Previously Ordered Items and General Items. Since you want these hidden at first, set their Visible properties to False. Then use code like this to make them appear when a customer is selected from the first combobox:

Private Sub ComboBox1_Change()
ComboBox2.Visible = True
ComboBox3.Visible = True
End Sub

Populating these comboboxes may be a little tricky, since there contents will depend on what the user selects from the customer combobox. I suggest that in a column next to each customer, you enter the range where a list of the items they previously ordered can be found. So if in A2 is the name Joe Smith and a list of the items he bought is on Sheet2 in cells A2:A5, in B2 enter 'Sheet2'!A2:A50.

Then as the Rowsource of your Previous Orders combobox you'll have to use a combination of INDIRECT and VLOOKUP.

Sorry I don't have time to go into more detail. A useform like this will probably take hours to create and require a lot of testing / trial & error. I would suggest getting started and getting as far as you can, then coming here with a specific question when you get stuck.

Good luck,

-Ben

Posted by Ben O. on July 11, 2001 3:33 PM

Sorry, put wrong name in Name field :) (n/t)