Display vlookup Data on userform

Yazdani

New Member
Joined
Jul 24, 2015
Messages
2
Hi,
i designed a userform for data entry using comboboxes and a textbox (blue in the image), the data selected in the white comboboxes will be multiplied by the number in the yellow comboboxes and the result to be displayed in the blue text box. everything works but the display gets nothing , not even an error just blank! in short this userform is a calculator without the number buttons but comboboxes as input, please help in getting data to display in the textbox! just as the user selects them(the default value in the yellow cmbbx's is "1") the code follows and the image.

Code:
Private Sub Activities_DropButt*******()
Me.Activities.List = Worksheets("ITEMS").Range("W4:W12").Value
End Sub
'Alcohols drop down lists
Private Sub Alcohols_DropButt*******()
Me.Alcohols.List = Worksheets("ITEMS").Range("AC4:AC33").Value
End Sub

'Beers dropdown lists click
Private Sub BEERS_DropButt*******()
Me.BEERS.List = Worksheets("ITEMS").Range("Z4:Z12").Value
End Sub

'Beverages drop down lists click
Private Sub Beverages_DropButt*******()
Me.Beverages.List = Worksheets("ITEMS").Range("Table7").Value         'Range("S4:S20")
End Sub
Private Sub ComboBox1_DropButt*******()

'Fill ComboBox
Me.ComboBox1.List = Worksheets("ITEMS").Range("Q4:Q54").Value
End Sub

Private Sub TextBox1_Change()
DISPLAY = Application.WorksheetFunction.VLookup(ComboBox1.Value, Worksheets("ITEMS").Range("Q4:Q54"), 2, False)
End Sub

Private Sub ComboBox5_Change()
Me.Beverages.List = Worksheets("ITEMS").Range("T4:T20").Value
End Sub


'Enter btn click
Private Sub CommandButton6_Click()

Dim emptyRow As Long

'Make Sheet2 active
Sheet2.Activate

'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

'Transfer information
Cells(emptyRow, 1).Value = ComboBox1.Value
Cells(emptyRow, 2).Value = Beverages.Value
Cells(emptyRow, 3).Value = BEERS.Value
Cells(emptyRow, 4).Value = Alcohols.Value

'Cells(emptyRow, 5).Value = Activities.Value
Cells(emptyRow, 6).Value = Now

'Clear comboboxes for next imput
ComboBox1.Clear
Beverages.Clear
BEERS.Clear
Alcohols.Clear

'Activities.Clear
HowMany.Clear
HowMany1.Clear
HowMany2.Clear
HowMany3.Clear
HowMany.Clear
ComboBox1.SetFocus
  
End Sub

Private Sub Combobx1_Select()
'DISPLAY = Application.WorksheetFunction.VLookup(ComboBox1.Value, (Worksheets("ITEMS").Range("L4:L54")), 2, False)
DISPLAY.Value = Application.WorksheetFunction.VLookup(ComboBox1.Value, Sheets("ITEMS").Range("L4:L54"), 2, False)
End Sub

'Close the App on Close btn click
Private Sub CommandButton7_Click()
Application.Quit
Unload Me

End Sub



Private Sub DISPLAY_Change()
'Dislay values.
Me.DISPLAY.Value = WorksheetFunction.VLookup(ComboBox1.Value, Sheets("ITEMS").Range("L4:L54"), 2, False)
'Me.DISPLAY.Value Beverages.Value
'Me.DISPLAY.Value = BEERS.Value
'Me.DISPLAY.Value = Alcohols.Value
'Me.DISPLAY.List = Activities.Value

'End Sub
End Sub

'fill How many

Private Sub HowMany_DropButt*******()
Me.HowMany.List = Worksheets("ITEMS").Range("Table5").Value
End Sub

'Fill how many1
Private Sub HowMany1_DropButt*******()
Me.HowMany1.List = Worksheets("ITEMS").Range("Table5").Value
End Sub
'Fill How many2
Private Sub HowMany2_DropButt*******()
Me.HowMany2.List = Worksheets("ITEMS").Range("Table5").Value
End Sub

'Fill how many 3
Private Sub HowMany3_DropButt*******()
Me.HowMany3.List = Worksheets("ITEMS").Range("Table5").Value
End Sub

Private Sub UserForm_Click()

End Sub
Image here
open
https://drive.google.com/open?id=0B8DVf56L4RxeZHJYaTZSaXdSaHc
Download XL file here
view
https://drive.google.com/file/d/0B-2y1kU3hw8bb1FLVGlGQURGeTA/view



 
Last edited:

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Forum statistics

Threads
1,214,826
Messages
6,121,797
Members
449,048
Latest member
greyangel23

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
Back
Top