Is there anyway to prevent an empty entry from showing up in a list box?
And pending that the above is possible is there a way to exclude the first value a column?
I want all values in column A except A! to display that aren't blank. Is this possible?
A couple of examples;
Ben Van Johnson
Thanks that might be helpful to me at some point but I believe I failed to mention that the listbox should be on a user form.
also what if the data to be validated is on another sheet?
Last edited by randell.graybill; 10-23-2009 at 01:43 AM.
Ok I figured that part out and probably will be easier then the other route I was going to go but I'd still like to know how to do this within a userform if you can help and thanks Leah.
Last edited by randell.graybill; 10-23-2009 at 01:26 AM.
Hello Randell,
Here is an example. This code would be placed in the general declarations section (the very top) of the UserForm. You will need tot change the worksheet name and ListBox to what you are using. These appear in red font.
Please Login or Register to view this content.
Sincerely,
Leith Ross
Remember To Do the Following....
1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.2. Thank those who have helped you by clicking the Starbelow the post.
3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
OK I will give that a try in a bit. Thanks Leith will update if this works for me.
Leith I put the code in the general procedure as you stated I should but nothing shows up in the listbox.
Hello Randell,
You should post your workbook then I can review the code to find the problem.
Leith here is the workbook
Hello Randell,
I am going to need some time to do this. You didn't tell me there were 3 UserForms being used to input data into a database.
Yeah my bad I find if I tell what is exactly going on and the extent of what is behind what I'm asking it tends to scare everyone away...so I try to get bits and pieces of info and make it work.
I sincerely appreciate your help sir.
Hello Randell,
Large or complex files don't scare me off. I just like to know what I am dealing with so I can scheduling my time accordingly.
Hello Randell,
Another point to consider when requesting help. If you have password protected anything in the workbook or VBA project, you should make that known. Either remove the protection or make the passwords available. Not a big deal in this case, more of an annoyance, because I have the software to break all Excel and VBA passwords.
Yeah my bad I had forgotten that I had any in there.
Hello Randell,
Okay, making some headway. I have rewritten the code for the "NewSales" user form. I created ListObjects on the "BrandsData" sheet to make compiling and retrieving the data. Have a look and let me know if this works for you or not. The other user forms haven't been touched.
NewSales Code
Please Login or Register to view this content.
Leith very close. The only things that are not right is when I click on the brand name in List Box for say brand ABC the same field is selected in the size listbox. If you look at the Brands sheet, Brand ABC has sizes 750ml and 500ml (updated to illustrate this possibility)only and not 250ml. The brand name list box functions correctly. The bottle size should be narrowed down based off of brand name not company name.
I'm sorry for this update, I did not foresee this problem until you got it working the way I was trying to do. Could you point in me the right direction where I can learn to make those lists you used? Maybe I can get the rest after I understand how to do that myself. I take what you guys show me here and try to learn it. I have learned quite a bit of things since April but I still have a great deal to learn and I appreciate everyones help on everything.
Uploading new verison with new formulas (mistaking omitted the first time) on the sales sheet columns G to N. Also added a button to fix broken formulas on the Commission Breakdown sheet (with something I learned how to do today in regards to the indirect(address) portion).
I feel like everything above is not making all that much sense so I will try to recap in case it doesn't make sense.
Basically some company's have the same brand in some sizes but not in other sizes. Company 1 has brand ABC with 750ml and 500ml. When Company 1 is selected it should show brand ABC and DEF. When Brand ABC is selected it should show all sizes brand ABC comes in.
I was thinking maybe this could be done by adding a sheet named BottleSData
Also do the lists on BrandsData update automatically?
Last edited by randell.graybill; 10-24-2009 at 01:57 AM.
Leith one thing I noticed after I coded the Enter Data button is that if the size is the same as the last size submitted then nothing is submitted to the cell. I will post an updated spreadsheet to show this new code. Also there is a space after the ml which is not consistent with the data on the Brands sheet. Due to the nature of the formulas in the sales sheet this must be consistent. I don't know how to change your lists to show it without the spaces.
I encountered some difficulty while trying to added the formulas to the cells parallel to the sales information specifically the one in column I.
see the attached sheet.
Last edited by randell.graybill; 10-24-2009 at 02:35 AM.
Hello Randell,
The company selection drives the brand data and bottle size according to the ListObjects contents. The bottle sizes were taken from your data on the "Brand" worksheet.
The ListObjects are very useful and under used.
1. Create your list
2. On the main menu go to Data > List > Create List...
3. A dialog will be displayed with some options.
The ListObject has many advantages. There is a built-in Sum row. You can filter the data just like using Auto-Filter without having to add it in. The range is automatically updated whenever you add or delete rows. You can even expand it to include adjacent columns.
When creating a database like this one, having UserForms is crucial for data input and output. It helps maintain the integrity of the database by preventing corruption of the data by direct worksheet input. You also need to be judicious in the use of worksheet formula. Use them sparingly to avoid problems and complications. Good database design doesn't need embedded code.
I set the bottle sizes to match the product selected. It made sense to have the available sizes shown when a product was selected. That is reason the brand lists include the bottle sizes. It would help to see a more complete sample list.
problem is I don't yet have a sample list. Making the prototype in order to test it with live data when I can get my hands on it. But some brands have different sizes and with the current setup only 1 size would be allowed to be chosen.
Hello Randell,
I have modified the "NewSale" user form. It now will display multiple columns for each product, such as, bottle size, weight, and piece count. This can be expanded to meet your needs. Have a look at the "BrandsData" worksheet to see how the ListObjects have been modified. This method eliminates the need for the other ComboBox and ListBox. I haven't written any code for the "enter Data" button yet. Let me know what you think about this.
NewSale User Form Code
Please Login or Register to view this content.
The way it is configured for brand 2 looks the closest to what it should the rest are wonky. The main function for this is to calculate how much money is going out to the employees via commission. Since the employees are 100% on commission and get credit for everything sold in the district regardless if it was their sale or not, thus how much commission is paid needs to be monitored and regulated...in the future other things may be needed. And I like the idea of the weight at this time it is not needed however.
I have uploaded the worksheet again with the formulas needed in the sales sheet, unless you can figure out a way to do what that sheet is doing without the formulas.
Thanks for the help by the way.
Hello Randell,
It appears we are not on the same page here. I am working on the sales part to present an example interface for that section. You seem to be jumping back and forth between different pieces and changing the code. If we are working in concert then this project will move forward. If not, nothing will be resolved.
I know you don't have a formal plan yet, but can you present me with an outline, step by step, with what you want to accomplish? You don't need to include code or formula, just the ideas.
Alright basically the sales sheet should show any sales that the sales reps get and the data that should be displayed is the detailed information about what they sold, company, brand, size and then the information pertaining to what they sold: units sold, how many bottles per case(Std UOM Conv), is it a case pack or a sample (Std UOM), (Standard Units of Measure/ Conversion) cost to the seller (cost), how much each case was sold for (variable based on cases sold), percentage comission for product sold, gross sales, and profit. The Comission breakdown sheet is a more detailed look at just the comission portion of the data on sales sheet. The brands sheet is the sheet which contains the information for each product sold; company, brand, bottle size (size), Std UOM and Std UOM Conv, CP1 (cost point 1), U/S (units sold) required to "hit" CP1, CP2 and U/S required to "hit" CP2, same for CP3 and U/S, and comission paid for when that product is sold. The brandsdata sheet is basically some of the same information from brands but layed out differently and only information needed to enter a new sale is on brands data.
That is how it currently setup, so the goal is to be able to have a worksheet where new brands can be added by the user, and new sales can be added to figure out comission. To put it in another way, the user will add what the sales reps sold and that information should be pulled from the available brands for any given company. what is needed on the sales sheet is person that sold it, how many sold, the information about the sale, eaches per case etc., how much commission is paid. How much that sale made the selling company (the company the sales rep works for) gross and net, after cost to the selling company and commission paid to the sales rep.
sorry for the walls of text.
Hello Randell,
Wanted to thank you for the last post. It is very helpful to see what you want done in an orderly fashion. I haven't had much time today to work on it, but will continue tomorrow.
Leith I wanted to thank you for your continued support on this.
Hello Randell,
I made a few changes to the Sales form I think you like. This version takes care of the "wonkies". I have added that word to my vocabulary. Anyway, I am cleaning up the other code as I code for the other parts. This is still going to take some time to finish and get it right. Give me your feedback and any ideas you have about the appearance, layout,etc.
Last edited by Leith Ross; 10-27-2009 at 09:38 PM. Reason: Updated Code
when I view the sales form and choose company 1 it shows an employee name under company name header, and a date under bottle sizes header.
When I choose company 2 it shows under company 2 header company 1 and under the bottle sizes header it shows the brands for company 1
and
When I choose company 3 under the company header it shows bottle sizes information, under bottle sizes header it shows units sold, under weight header it shows Std UOM Conv information, under pieces header it shows whether or not it is case or a single (cases = CA, singles = Eaches)
Ok weird I reloaded the user form like 3 times and the information loaded correctly but initially it was showing the information as described above.
Two comments I have is the header for the company name I would like if possible for it to say: "Company 1 (Brands)" or just Brands since the drop down box to the left already establishes that the information is for company 1
Also the formulas I had in the spreadsheet, I would like something along the lines of that functionality added, either formulas to grab the information and then a paste special to get rid of the formula, or when the sales information is added to have the other background information added as well. The user doesn't need to see this information before it is inputted into the sheet.
After hitting enter data on the sales form, all of the information on the sales sheet that I have in place already, should be shown pertaining to what the user inputed. After further consideration I believe that there should be a choice to show bottles per case since it is possible that a brand might have the same bottle size, flavor and everything and the only difference could come down to how many bottles are in the case. I think you call this pieces. So ultimately what the user needs to choose is brand, bottle size, and bottles per case (pieces) and the rest should be populated for them automatically.
Does all of that make sense?
Hello Randell,
I'll change the header. I haven't really looked at all the formulas on the sheet. When you use UserForms for input, you generally can do away with most, if not all, formulas. I will keep the functionality of the worksheet intact using formauls and/or VBA macros.
you should have a copy of the workbook(s) with the formulas on this thread if you need to look at them to know exactly what I'm talking about. And again thanks Leith.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks