I am trying to design a spreadsheet to allow user input directly into Excel, but am a VBA newbie!
I have reached a point where I think (?!) I may need a user form (rather than a data validation list) to present a matrix of choices to the user.
The draft VBA user form shows the initial sales categories (sportswear, sports equip, etc) here
As each button is pressed, the related subcategory choice would appear in the list box. The categories and subcategories are summarised here for simplicity
Excel 2007
What I need returned in each cell of col S (Nominal Code) below is the nominal code (4000, etc) relating to the user's selection of category/sub category from the input form. The form should "pop up" on each selection of a cell in col G (Sales category) then "hide" when the cursor moves off col G. A view of the input section of the worksheet is given here
Excel 2007
I hope I've used this forum correctly and used the HTLM editor as intended - apologies if I haven't! Any help gratefully appreciated. (Win 7 Home Prem & Excel 2007)
I have reached a point where I think (?!) I may need a user form (rather than a data validation list) to present a matrix of choices to the user.
The draft VBA user form shows the initial sales categories (sportswear, sports equip, etc) here
As each button is pressed, the related subcategory choice would appear in the list box. The categories and subcategories are summarised here for simplicity
| Excel Workbook | ||||
|---|---|---|---|---|
| AT | AU | |||
| 494 | Sports clothing Nom Codes | |||
| 495 | Clothing | |||
| 496 | Rugby | 4000 | ||
| 497 | Cricket | 4001 | ||
| 498 | Football | 4002 | ||
| 499 | Other | 4003 | ||
| 500 | Sports equip Nom Codes | |||
| 501 | Equip | |||
| 502 | Rugby | 4100 | ||
| 503 | Cricket | 4101 | ||
| 504 | Football | 4102 | ||
| 505 | Other | 4103 | ||
| 506 | Schoolwear Nominals | |||
| 507 | Schools | 4200 | ||
| 508 | Colleges | 4201 | ||
| 509 | Other | 4202 | ||
| 510 | Workwear Nominals | |||
| 511 | Basics | 4300 | ||
| 512 | Dickies | 4301 | ||
| 513 | Mascot | 4302 | ||
| 514 | Helly Hanson | 4303 | ||
| 515 | Snickers | 4304 | ||
| 516 | Caterpillar | 4305 | ||
| 517 | Other | 4306 | ||
| 518 | Other Nominals | |||
| 519 | Internet | 4400 | ||
| 520 | Counter | 4500 | ||
| 521 | Other | 4900 | ||
Input | ||||
What I need returned in each cell of col S (Nominal Code) below is the nominal code (4000, etc) relating to the user's selection of category/sub category from the input form. The form should "pop up" on each selection of a cell in col G (Sales category) then "hide" when the cursor moves off col G. A view of the input section of the worksheet is given here
| Excel Workbook | |||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| G | H | I | J | K | L | M | N | O | P | Q | R | S | |||
| 1 | SALES CATEGORY | QTY | UNIT COST | UNIT SALE | UNIT PROFIT | UNIT GP% | QUOTE VALUE | QUOTE PROFIT | QUOTE GP% | TAX CODE | PROD CAT | DEPT ANAL | NOMINAL CODE | ||
| 2 | 12 | 5.50 | 8.46 | 2.96 | 35.00% | 101.54 | 35.54 | 35.00% | T0 | 1 | 1 | ||||
| 3 | 15 | 5.60 | 8.62 | 3.02 | 35.00% | 129.23 | 45.23 | 35.00% | T0 | 1 | 1 | ||||
| 4 | 36 | 5.70 | 8.77 | 3.07 | 35.00% | 315.69 | 110.49 | 35.00% | T0 | 1 | 1 | ||||
| 5 | 20 | 5.75 | 8.85 | 3.10 | 35.00% | 176.92 | 61.92 | 35.00% | T0 | 1 | 1 | ||||
| 6 | 15 | 5.75 | 8.85 | 3.10 | 35.00% | 132.69 | 46.44 | 35.00% | T1 | 1 | 1 | ||||
| 7 | 60 | 5.80 | 8.92 | 3.12 | 35.00% | 535.38 | 187.38 | 35.00% | T1 | 1 | 1 | ||||
| 8 | 80 | 5.80 | 8.92 | 3.12 | 35.00% | 713.85 | 249.85 | 35.00% | T1 | 1 | 1 | ||||
| 9 | 75 | 5.85 | 9.00 | 3.15 | 35.00% | 675.00 | 236.25 | 35.00% | T1 | 1 | 1 | ||||
| 10 | 25 | 6.00 | 9.23 | 3.23 | 35.00% | 230.77 | 80.77 | 35.00% | T1 | 1 | 1 | ||||
| 11 | 12 | 6.25 | 9.62 | 3.37 | 35.00% | 115.38 | 40.38 | 35.00% | T1 | 1 | 1 | ||||
| 12 | 12 | 7.00 | 10.77 | 3.77 | 35.00% | 129.23 | 45.23 | 35.00% | T1 | 1 | 1 | ||||
| 13 | |||||||||||||||
| 14 | |||||||||||||||
Input | |||||||||||||||
I hope I've used this forum correctly and used the HTLM editor as intended - apologies if I haven't! Any help gratefully appreciated. (Win 7 Home Prem & Excel 2007)
