combobox return numerical value for further calculations with other userform functions

luihp329

New Member
Joined
Jan 20, 2021
Messages
16
Office Version
  1. 2016
Platform
  1. Windows
Hi,

I am fairly new to VBA and I need to quickly construct a simple userform for work. To put it simply, I have a list of items each with corresponding price (2 groups, one group is a subset of the other group of major items), I would like to construct a userform where the user can use a combobox to select from the major items, in which the corresponding price of the selected item would be multiplied by a value which is inputted via a textbox on the userform.

I had a good start where I figured out how to input all the major items into the combobox and it works when I press F5. However, I am stuck now when I want to return the corresponding value for formula operations with a textbox input, as I believe this requires some coding. The items and their prices are already listed in a worksheet in adjacent columns, in a way such that a vlookup function can be used normally to return the price values based on specific conditions. Appreciate it if I can be pointed to the right directions, thank you.
 

luihp329

New Member
Joined
Jan 20, 2021
Messages
16
Office Version
  1. 2016
Platform
  1. Windows
We can use combobox to pick the main category and a listbox to pick (multi select) the sub category.
I've downloaded your last sample. But I don't quite understand the data layout.
Let's say you pick "Ward Renovation (General)" as the main category in the combobox then what items on your example should be shown for you to pick next (in the listbox)?
You should arrange your data so we can know which items belong to which main category.
Something like this:

Book1
ABC
1Main CategoryItemcost
2Isolation Room Renovation/Conversion (Tier 1)Ceramic Wall Tiles10
3Isolation Room Renovation/Conversion (Tier 1)Homogenous Wall Tiles20
4Isolation Room Renovation/Conversion (Tier 1)Wall Waterproofing30
5Ward Renovation (General)Anti-mould Coating40
6Ward Renovation (General)Proprietary Elastic Acrylic Silicon Resin Colour Coating to plastered surface50
7Ward Renovation (Specialty)False Ceiling System (Frame)60
8Ward Renovation (Specialty)False Ceiling System (Panel/paper)70
Sheet1


so in my example only 2 items belongs to "Ward Renovation (General)":

Anti-mould Coating
Proprietary Elastic Acrylic Silicon Resin Colour Coating to plastered surface
Hey sorry didnt go on this forum this morning, I did some reading online last night and it seems like two listboxes side by side with "remove" and "add" button seems to be most suitable for my case, as all of my major items have already included some minor items with the option to add more, and also when it comes to it there are only 5 to 6 minor items that are concerned, so with this method there is an option to remove items. Please have a look at the attached spreadsheet here Ive drawn up the additional controls and some initial codes that dont seem to work because I suck with even the basic language :(. Basically I want to use the Private Sub ComboBox1_Change() function to generate unique lists depending on what was initially selected, then the available items can be either added (moved to the right) or removed (moved back to the left) and the respectively cost values added or subtracted from the final cost after mutiplication by the area input. I think as there are only 6 different minor cost items ( that would appear in the lists) I think we can just define their values inside the codes? But I mean what they are dosent matter we can figure out the general format and then add them in after? The new codes to generate the unique lists based on combobox selection I have tired what i can at the top, please have a look, i think the most simple and method is to exhaustively use the IF function for 9 times until all major room reno types have been covered from B4 to B14, there should be a simplier method though, especially if we need to write further codes for user inputs on the two lists. Thanks again for your help.

 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,961
Office Version
  1. 365
Platform
  1. Windows
What's the difference between item in listbox1 & listbox2?

I think as there are only 6 different minor cost items ( that would appear in the lists) I think we can just define their values inside the codes?
Not a good idea, the data should be on the sheet, it will be easier to maintain.
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,961
Office Version
  1. 365
Platform
  1. Windows
Here's the data layout that I think suit your needs:
luihp329 - example.xlsm
ABCD
1BOXFRUITcodeCOST
2BOX1Apple110
3BOX1Apricots120
4BOX1Avocado230
5BOX2Banana140
6BOX2Blackberries150
7BOX2Blackcurrant260
8BOX2Blueberries270
9BOX2Breadfruit280
Sheet1


Column "Code" means to identify item 1 & item 2.

Edit: if you can arrange your data like the example above , I think I can help you to write the code.
 

luihp329

New Member
Joined
Jan 20, 2021
Messages
16
Office Version
  1. 2016
Platform
  1. Windows
What's the difference between item in listbox1 & listbox2?


Not a good idea, the data should be on the sheet, it will be easier to maintain.
listbox2 shows currently included sub-items for the standard price and listbox 1 shows items not included yet, like "extra options". For example say for general ward renovation, for simplicity say the standard price already includes sub items A B C D and does not include E F G H, so I would like it when i select general ward renovation, Listbox2 will display ABCD and listbox 1 will display EFGH, and i can move any of the 8 items from 1 to 2 or 2 to 1, either adding extra options or removing originally interluded items. But the thing is I want unique listbox1 or listbox2 items for each of the renovation type options in combobox, does that mean I would need to make such a table for each major renovation type in the spreadsheet, or can column A be omitted and listbox items for each renovation type defined within the codes?
 

luihp329

New Member
Joined
Jan 20, 2021
Messages
16
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Here's the data layout that I think suit your needs:
luihp329 - example.xlsm
ABCD
1BOXFRUITcodeCOST
2BOX1Apple110
3BOX1Apricots120
4BOX1Avocado230
5BOX2Banana140
6BOX2Blackberries150
7BOX2Blackcurrant260
8BOX2Blueberries270
9BOX2Breadfruit280
Sheet1


Column "Code" means to identify item 1 & item 2.

Edit: if you can arrange your data like the example above , I think I can help you to write the code.
Sorry I dont understand code 1 and 2 and its relationship with fruit and box
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,961
Office Version
  1. 365
Platform
  1. Windows
Sorry, I don't know how to implement the userform with 2 listbox as your requirement.
And it's actually a different problem from the original, so I suggest you start a new thread for the problem, it will attract more helpers to find a working solution.
 

luihp329

New Member
Joined
Jan 20, 2021
Messages
16
Office Version
  1. 2016
Platform
  1. Windows
Sorry, I don't know how to implement the userform with 2 listbox as your requirement.
And it's actually a different problem from the original, so I suggest you start a new thread for the problem, it will attract more helpers to find a working solution.
ok bro, appreciate your help so far, thank you.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,387
Messages
5,624,389
Members
416,026
Latest member
melvic69

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top