Segregate price list on quote form

Hi,

I take it you are E-mailing the file to the customer?

You could use VBA to do a paste special picture in word so the document cannnot be altered, save it under the quotation number. Once done, the user would have to go back and send as an attachment. Not fool proof though, because I am doing this now but found that some users cannot do the paste special (still haven't figured out why).

Another route is to hide all the columns on the pricelist sheet and then protect it with a password. Hide all formulas on the Quoteform and lock any cells that do not want someone to see or alter and protect this sheet with a password as well.

Ron

Navin,

You can do this in one file. Here's how
1. Create worksheet with two tabs, delete the other tabs.
2. In Sheet2 create your data table (pricelist). I will assume that Col A will be the product names and Col C will be the retail/customer pricing.
3. Paste Sheet2, Col A to somewhere out of the way on Sheet1, Like Col AA. (I am assuming that customers are allowed to see the entire list of products that you carry).
4. rename Sheet2 to something very difficult to guess (I'll refer to it as difficult!)
5. In Sheet1!A1 create a data validation list to your product list on Col AA (or whereever u put it)
6. Sheet1!B1 should be =vlookup(A1,Difficult!A1:D4,3,FALSE). I am assuming the retail pricing is in Col C. Also, Change D4 to whereever the table ends.

Now whenever you change the product in the A1 drop down, B1 gives you the customer price. That's the easy part, right. Now for the security.
1. Activate Difficult! and then goto Tools|Protection|Protect Sheet. Clear all checkboxes, enter a password and click ok.
2. Goto VBE (Alt+F11) and in the project window click on Sheet2(difficult). Now in the properties window (F4 if not visible) change the Visible property to "2-xlSheetVeryHidden". Go back to Excel, can u see difficult!? No? good.
3. In VBE, right click on VBAProject(quote.xls) and choose project properties. Goto Protection tab, lock for viewing and choose a password (do not forget it!!!) and close VBE
4. Back in Excel, Select A1 and go Tools|Protection|Allow Users to Edit Ranges. Click New. Refers to cells should be =$A$1, then click Ok, and Ok again. This will allow users to change A1 after we protect the sheet.
5. Go Tools|Protection|Protect Sheet. IMPORTANT: Only "Select unlocked cells" should have a check mark. Fill in a password and hit Ok.
6. (Optional) Go Tools|Protection|Protect Workbook. Structure should have a check mark. enter a password and click ok.

Now the user can only drop down Sheet1!A1 to get the retail price result in B1, they cannot see your pricing or anything else. They can't enter formulas or their own text. Nothing except choose from a list!

I'm sure that you will want to modify to fit your form, there are a couple of important things here. The user should not be allowed to see any formula that refers to difficult!. If they see the sheet name they can read your data. The best method is to only allow them access to drop down lists.

Also, if you forget the VBA password, you will never get access to your pricing table back.

Hope this helps, let me know if you have any Q's. I'd be interested if this works for you.



P.S. Great user name, one of my favorite movies!

Ron, sorry, but if someone knows what they are doing they can get around the second method you mentioned is less than 30 seconds.

Hi

I am also creating a quote generator and saw your message as below.

I am asking your help - I am also trying to create a easy to use & quick quote sheet for my sales staff - but my problem is slightly different and I just need to be pointed in the right direction.

I also have about a 1000 items - but only 15 or so product categories and trying to build a filter for them to quote items. that is to select the category from a list and then the second list will show all the items in that category.
Once those two fields are selected I will then do a V-liook up - but do not know how to link the two fields like this.

Might you know a function to use?

Navin R Johnson said:

I am a novice excel user looking for a little guidance:

I built a quote generator in Excel but have questions about making it user friendly for my colleagues while protecting confidential data from the outside world.

BACKGROUND:
The quote form is based on our price list. There are ~1,000 items on our price list. Each quote typically has about 20 line items. I used data validation to create lists of part numbers in various categories. When a part number is selected VLOOKUP fills in the cells in that row with Description, List Price and Discounted Price. As it is right now everything is in one file. The quoteform exists on sheet 1 and the price list is sheet 2.


QUESTIONS:
How do I protect confidential data from prying eyes? Not only do I want to hide the price list (with confidence that a clever user won't dig it up) I also want to disable the lists so that a customer can't simply use the quoteform as a tool to browse our price list.

One method seems to be to separate the price list from the quote generator but that seems to bring up a few challenges.

1) If the sheets are separated into QuoteForm.xls and PriceList.xls I believe PriceList.xls needs to be open for QuoteForm.xls to work, correct? Having to open two files might confuse or annoy my co-workers. Is there a way to automatically spaun PriceList.xls everytime QuoteForm.xls is opened by a trusted user?

2) If I go this route, customers will never have access to PriceList.xls unless I send it to them. The QuoteForm will still display the last set of data that was entered, correct? BUT won't customers get a message that "this file references data in a separate worksheet..."? Is there any way to avoid this?

Another option that I am considering is to embed the quote generator into a Word document but that seems to bring up its own series of problems.

Any advice would be appreciated, (even links to relevant threads).

Click to expand...