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
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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
 
Upvote 0
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 :)
 
Upvote 0
I'm glad to help you. Thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

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