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.
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,961
Office Version
  1. 365
Platform
  1. Windows
Hi, @luihp329 Welcome to the Forum
Could you upload a sample workbook (without sensitive data) to a free site such as dropbox.com or google drive & then share the link here?
It will make it easier to test and find a solution.
 

luihp329

New Member
Joined
Jan 20, 2021
Messages
16
Office Version
  1. 2016
Platform
  1. Windows
Hi, @luihp329 Welcome to the Forum
Could you upload a sample workbook (without sensitive data) to a free site such as dropbox.com or google drive & then share the link here?
It will make it easier to test and find a solution.
Please see below, thanks.
未命名.png

Basically I have inputted everything in Column B into the combobox, I just want it to return the value in column C and do operations with a textbox input that i will make in the userform (Area) and return the final value with a command button. Please let me know if you need more details, thanks for your help!!
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,961
Office Version
  1. 365
Platform
  1. Windows
Sorry, I don't want to recreate the userform & retype the sample data, that's why I asked you for a sample workbook.;)
So I hope another member will step in to help you.
 

luihp329

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

ADVERTISEMENT

Sorry, I don't want to recreate the userform & retype the sample data, that's why I asked you for a sample workbook.;)
So I hope another member will step in to help you.
Ok sorry I was trying to get rid of sensitive data and save as a new file but some error came up, I think I know what is wrong now give me a second I will attach it, is it ok for me to attach it here or you prefer a link? Thanks.
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,961
Office Version
  1. 365
Platform
  1. Windows
You can't attach a file in this forum so upload it to a free site such as dropbox.com or google drive & then share the link here
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,961
Office Version
  1. 365
Platform
  1. Windows
Try this:
1. I change the sheet name with unicode char to "sheet2", because it doesn’t work with my local settings.
2. Variable x is where I put the value of the corresponding price (col H)

VBA Code:
Dim va, vb

Private Sub CommandButton1_Click()
Dim x, fm

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
    End If
End With
y = Cells(fm, "H").Address 'the cell where you 'get the value in col H

MsgBox x

End Sub

Private Sub UserForm_Initialize()
    
    With Sheets("sheet2")
        ComboBox1.List = .Range("B4", .Cells(.Rows.Count, "B").End(xlUp)).Value
    End With

End Sub

the workbook
 

luihp329

New Member
Joined
Jan 20, 2021
Messages
16
Office Version
  1. 2016
Platform
  1. Windows
Try this:
1. I change the sheet name with unicode char to "sheet2", because it doesn’t work with my local settings.
2. Variable x is where I put the value of the corresponding price (col H)

VBA Code:
Dim va, vb

Private Sub CommandButton1_Click()
Dim x, fm

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
    End If
End With
y = Cells(fm, "H").Address 'the cell where you 'get the value in col H

MsgBox x

End Sub

Private Sub UserForm_Initialize()
  
    With Sheets("sheet2")
        ComboBox1.List = .Range("B4", .Cells(.Rows.Count, "B").End(xlUp)).Value
    End With

End Sub

the workbook
 

luihp329

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

However I am getting a type mismatch error 13 with debugging showing something wrong with this line:
y = Cells(fm, "H").Address

thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,127,379
Messages
5,624,351
Members
416,022
Latest member
JonathanSass

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