manuthenunkal

New Member
Joined
Aug 25, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
HI Excel Expert ,

Request your valuable help in solving the below error .

PS: Am new to Excel VBA coding .

I have got 3 Sheets in the attached workbook “Just combo V2”

Download link : Just combo V2.xlsm

Sheet 1

Sheet 2 =”School” in which Cell A1 : A14 Contains list of schools

Sheet 3 =”Prd” in which B1 to B29= Description , H1 to H29 = Selling Price of each product .

In Sheet 1 ,While the “Add new record” button is pressed , The user form is initiated which prompts us to choose from the drop down list of “school” taken from Sheet “School “ , Choose Product from the drop button which list description of products ( Cell B1 to B29 of Prd ).Qty is to be entered in the text box . Upon clicking :Submit button of the user form ,In Sheet 1 - I would like to see the corresponding selling price of the product in the Cell G .I have tried Vlookup function in different ways and its always showing error .

Sheet is password protected and password is 1234 :)

Many Thanks in Advance
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
Hi and welcome to MrExcel.

No need to search.
We can use the index of the combobox2.
Products are loaded from row 2 of sheet "Prd". If you select the first data of the combo, the index is 0, if you add 2 to 0, we know that this data is in row 2, then we take the price of row 2 column F.
Change all your code to the following:

VBA Code:
Private Sub CommandButton1_Click()
'''''''''Valdiation 1 ''''''
  If Me.ComboBox1.Value = "" Or ComboBox1.ListIndex = -1 Then
    MsgBox " Please select the School ", vbCritical
    Exit Sub
  End If
  If Me.ComboBox2.Value = "" Or ComboBox2.ListIndex = -1 Then
    MsgBox " Please select the Product ", vbCritical
    Exit Sub
  End If
  If VBA.IsNumeric(Me.TextBox3.Value) = False Then
    MsgBox "Please enter the correct qty ", vbCritical
    Exit Sub
  End If
  
  Dim sh As Worksheet
  Dim lr As Long, n As Long
  
  Set sh = Sheets("sheet1")
  lr = sh.Range("D" & Rows.Count).End(3).Row + 1
  n = Val(sh.Range("A" & lr - 1).Value) + 1
  
  sh.Range("A" & lr).Value = n
  sh.Range("D" & lr).Value = Me.ComboBox1.Value
  sh.Range("E" & lr).Value = Me.ComboBox2.Value
  sh.Range("F" & lr).Value = Me.TextBox3.Value
  sh.Range("G" & lr).Value = Sheets("Prd").Range("F" & ComboBox2.ListIndex + 2)
  sh.Range("H" & lr).Value = sh.Range("F" & lr).Value * sh.Range("G" & lr).Value
  
  MsgBox "Done", vbInformation
  Call CommandButton2_Click
End Sub

Private Sub CommandButton2_Click()
  Me.TextBox3.Value = ""
  Me.ComboBox1.Value = ""
  Me.ComboBox2.Value = ""
End Sub

Private Sub UserForm_Activate()
  Dim i As Integer
  Me.ComboBox1.Clear
  Me.ComboBox2.Clear
  
  For i = 2 To ShSchool.Range("A" & Rows.Count).End(xlUp).Row
    Me.ComboBox1.AddItem ShSchool.Range("A" & i).Value
  Next i
  
  For i = 2 To ShPrd.Range("B" & Rows.Count).End(xlUp).Row
    Me.ComboBox2.AddItem ShPrd.Range("B" & i).Value
  Next i
End Sub
 

manuthenunkal

New Member
Joined
Aug 25, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Dear Dante ,

Many Thanks for your quick feedback !

Now the code works absolutely fantastic . A small change which i made to the code is for populating the sl# as it was giving me errors .

Rest everything is perfect and neat now .

Once again Many Thanks as i was stuck up with this single line for past 3 days :)
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
I'm glad to help you. Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,139
Messages
5,546,177
Members
410,731
Latest member
keobongmacao
Top