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.
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,961
Office Version
  1. 365
Platform
  1. Windows
Ah, you're right. It should be:

VBA Code:
Dim va, vb

Private Sub CommandButton1_Click()
Dim x, fm
Dim y As String

With Sheets("sheet2")
fm = Application.Match(ComboBox1.Value, .Range("B:B"), 0)
    If IsNumeric(fm) Then
        x = Cells(fm, "H").Value 'get the value in col H
        y = Cells(fm, "H").Address 'the cell where you 'get the value in col H
    End If
End With

MsgBox x

End Sub
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

luihp329

New Member
Joined
Jan 20, 2021
Messages
16
Office Version
  1. 2016
Platform
  1. Windows
Sorry, that issue was fixed once I used your code for the combobox too instead of my own codes. Now I will see if I can try on my own to get the output to show the product of the textbox input* H haha.
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,961
Office Version
  1. 365
Platform
  1. Windows
Sorry, that issue was fixed once I used your code for the combobox too instead of my own codes.
Still, you should use the new code because the old code will generate error if the user type something in the combobox that isn't on the list and then hit the command button.
 

luihp329

New Member
Joined
Jan 20, 2021
Messages
16
Office Version
  1. 2016
Platform
  1. Windows
Still, you should use the new code because the old code will generate error if the user type something in the combobox that isn't on the list and then hit the command button.
Got it, yep, used your code instead. The textbook part seems to be more complicated than i thought, do i have to put the Private Sub TextBox1_Change() portion ahead of the commandbutton click portion for this to work?
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,961
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Try something simple:
I changed the blue line.
  1. select an item in combobox
  2. type 888 in the textbox
  3. hit command button

Rich (BB code):
Private Sub CommandButton1_Click()
Dim x, fm
Dim y As String

With Sheets("sheet2")
fm = Application.Match(ComboBox1.Value, .Range("B:B"), 0)
    If IsNumeric(fm) Then
        x = Cells(fm, "H").Value 'get the value in col H
        y = Cells(fm, "H").Address 'the cell where you 'get the value in col H
    End If
End With

MsgBox x + TextBox1.Value

End Sub
 

luihp329

New Member
Joined
Jan 20, 2021
Messages
16
Office Version
  1. 2016
Platform
  1. Windows
Try something simple:
I changed the blue line.
  1. select an item in combobox
  2. type 888 in the textbox
  3. hit command button

Rich (BB code):
Private Sub CommandButton1_Click()
Dim x, fm
Dim y As String

With Sheets("sheet2")
fm = Application.Match(ComboBox1.Value, .Range("B:B"), 0)
    If IsNumeric(fm) Then
        x = Cells(fm, "H").Value 'get the value in col H
        y = Cells(fm, "H").Address 'the cell where you 'get the value in col H
    End If
End With

MsgBox x + TextBox1.Value

End Sub
oh wow it does work, no need formulae or additional dims, I used * instead because H is value per meter squared. Thanks for your help so far, now comes the hard part, so these are the major cost items. Now I want a third input where i can add or subtract minor cost items to get more accurate cost estimate figures. Looks like a cheklist type option is most suitable, like you know those ones with left and right <Add > Remove, etc. However I want each individual major items (after initial selection from combobox) to display their unique and limited minor items that can be added or subtracted by the user, which will be added or subtracted before display in the msg box. Do you have any ideas to get me started? cheers. Maybe an interim command button is required to firstly generate the unique list and then the final button to display adjusted cost figure after options checked.
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,961
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I don't understand what you're describing. Could you explain in more detail by using an example.

to display their unique and limited minor items
Do you already have this data? where?
 

luihp329

New Member
Joined
Jan 20, 2021
Messages
16
Office Version
  1. 2016
Platform
  1. Windows
I don't understand what you're describing. Could you explain in more detail by using an example.


Do you already have this data? where?
Yes I do but not in the file i gave earlier, its simply just more items in column B and H further down, i guess later we just need to modify the cell references from $B $H to limiting from which rows to which rows too.

For example you know how right now i just select the renovation type such as general wards and type in the ward area and it returns the estimated cost of doing the renovations? But in reality each project is slightly different, depending on if certain sub systems are included, such as air conditioning, medical cases, cabinets, etc. So I would like to have some of those items listed on a list box so if the user knows what items will or will not be included he would be able to check or uncheck those items before generating a more accurate cost estimation, as I have the cost data of those standalone items so they can be added or subtracted to the initial estimate. But is it true that I can only have a set list of the same items no matter what i pick in the combobox? Or can I generate different lists depending on what i pick from the initial combobox? I will give u an example file in a sec.
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,961
Office Version
  1. 365
Platform
  1. Windows
Or can I generate different lists depending on what i pick from the initial combobox?
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,368
Messages
5,624,286
Members
416,018
Latest member
mirceaon

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