Excel VBA UserForm Vlookup error

Question

Alright, so here’s the code for my UserForm:

Private Sub CancelButton_Click()

Unload Me

End Sub

Private Sub ClearButton_Click()

Call InventoryEntryBox_Initialize

End Sub

Private Sub SubmitButton_Click()
Dim emptyRow As Long

'Make Inventory Test sheet active
Worksheets("InventoryTest").Activate

'Transfer Information
Worksheets("InventoryTest").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = PartNumberComboBox.List
Worksheets("InventoryTest").Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = LocationTextBox.Value
Worksheets("InventoryTest").Cells(Rows.Count, "D").End(xlUp).Offset(1, 0).Value = QuantityTextBox.Value
Worksheets("InventoryTest").Cells(Rows.Count, "F").End(xlUp).Offset(1, 0).Value = CommentsTextBox.Value

Call InventoryEntryBox_Initialize

End Sub

Private Sub InventoryEntryBox_Initialize()

'Fill PartNumberComboBox
PartNumberComboBox.List = ActiveWorkbook.Sheets("Test2").Range("B2:B43").Value

'Empty Location Text Box
LocationTextBox.Value = ""

'Empty Quantity Text Box
QuantityTextBox.Value = ""

'Empty Comments Text Box
CommentsTextBox.Value = ""

'Unit Of Measure auto-fill box


'Description auto-fill box
DescriptionFormula.Value = Application.WorksheetFunction.VLookup(PartNumberComboBox.List, ActiveWorkbook.Sheets("Test2").Range("B2:D43"), 3, False)

'Set focus on Empty Part Number text box
PartNumberTextBox.SetFocus

End Sub

Now, the idea with this code is to have User Form so someone can fill it out (the PartNumberComboBox, LocationTextBox, QuantityTextBox, and CommentsTextBox) in order to catalogue inventory on what items are in what places and in what quantity. When someone fills out an item number in the PartNumberComboBox, the DescriptionFormula will auto-fill with data from a list (Noted as PartDescription, which includes D2:D43 in the sheet Test2).

My problem, however, is two-fold: The DescriptionFormula.Value box does not auto-fill with data when someone enters in a part number in the PartNumberComboBox, and when I try to hit the “submit” button to confirm the data entered in the form and put it in the areas specified in the code, I get a dialogue box that pops up and says “Run-time error 70: Permission Denied”, then the "PartNumberComboBox.List = ActiveWorkbook.Sheets("Test2").Range("B2:B43").Value" is highlighted when I go to debug.

I’m not sure if it’s a problem with my code, or if it’s a limitation of Excel or Vlookup based on what I’m doing… or if it’s something else. Any kind of help anyone could offer would be a blessing at this point.

Change "PartNumberComboBox" combobox property Style=2 - fmStyleDropdownList — Arya
Changing that removes the ability for the user to see what they're typing in the combo box, and still gives me the "permission denied" error. — Allenisto
in column B you want only one value from combobox right??? use PartNumberComboBox.List(PartNumberComboBox.listindex) or PartNumberComboBox.text — Arya

Allenisto Allenisto · Accepted Answer · 2015-04-09T17:37:43

I've actually solved my own question:

So it turns out that I needed to write a new sub for this particular equation, not embed it in my Initialize sub. Here's what I wrote to get it to work:

Private Sub PartNumberComboBox_Change()
Me.UnitOfMeasureFormula.Value = Application.WorksheetFunction.VLookup(Me.PartNumberComboBox.Value, Sheets("sheet2").Range("a2:c43"), 3, False)
Me.DescriptionFormula.Value = Application.WorksheetFunction.VLookup(Me.PartNumberComboBox.Value, Sheets("sheet2").Range("a2:c43"), 2, False)
End Sub

while changing my "Description auto-fill box" to this:

'Empty Description label
DescriptionFormula.Value = ""

What this does is empty out the box that the auto-fill will go in on startup of the Userform, then using a _Change sub with the ComboBox that the formula is referencing so it alters the DescriptionForumula based on what the ComboBox says with a simple VLookup formula. Now it's working smoothly and I'm back on track!

Excel VBA UserForm Vlookup error

1 Answers