combobox return numerical value for further calculations with other userform functions

[luihp329]

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]

Ah, you're right. It should be:

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

 

[luihp329]

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]

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]

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]

Try something simple:
I changed the blue line.

1. select an item in combobox
2. type 888 in the textbox
3. hit command button

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]

Try something simple:
I changed the blue line.

1. select an item in combobox
2. type 888 in the textbox
3. hit command button

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]

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]

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.

 

[luihp329]

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?

luihp329 = Cost Benchmarking V1 (Sample).xlsm

Shared with Dropbox

www.dropbox.com

 

[Akuini]

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
	A	B	C
1	Main Category	Item	cost
2	Isolation Room Renovation/Conversion (Tier 1)	Ceramic Wall Tiles	10
3	Isolation Room Renovation/Conversion (Tier 1)	Homogenous Wall Tiles	20
4	Isolation Room Renovation/Conversion (Tier 1)	Wall Waterproofing	30
5	Ward Renovation (General)	Anti-mould Coating	40
6	Ward Renovation (General)	Proprietary Elastic Acrylic Silicon Resin Colour Coating to plastered surface	50
7	Ward Renovation (Specialty)	False Ceiling System (Frame)	60
8	Ward 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