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.
Sub LoadListBox() Dim Cell As Range Dim Rng As Range Dim RngEnd As Range Dim Wks As Range Set Wks = Worksheets("Sheet1") Set Rng = Wks.Range("A2") Set RngEnd = Wks.Cells(Rows.Count, Rng.Column).End(xlUp) Set Rng = IIf(RngEnd.Row < Rng.Row, Rng, Wks.Range(Rng, RngEnd)) For Each Cell In Rng If Cell <> "" Then ListBox1.AddItem Cell.Text End If Next Cell End Sub
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.
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?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks