Dear Expert,
In the userform in my attached file, i have a combobox & if i select a data from combo & click "Show details" button then user form other txtbox data show me combobox releated data. example: if i select a data from combobox as OGSQA 1282 then click " Show details" button then order qty show 10000 & SMV show 16.05 as other field also.
Another thing, in combobox i just want to select or enter match character data. not enter any new character. please help me.
Best regards
Wahid
Hello,
Try,,
Option Explicit
Private Sub ComboBox1_Change()
Dim ws As Worksheet
Dim rngItem, firstAddress
Set ws = Sheets("Sheet2")
With ws.Range("A2:A" & ws.Cells(ws.Rows.Count, 1).End(xlUp).Row)
Set rngItem = .Find(Me.ComboBox1.Value, LookIn:=xlValues)
If Not rngItem Is Nothing Then
firstAddress = rngItem.Address
Do
Me.TextBox1.Value = rngItem.Offset(, 1).Value
Me.TextBox2.Value = rngItem.Offset(, 2).Value
Set rngItem = .FindNext(rngItem)
Loop While Not rngItem Is Nothing And rngItem.Address <> firstAddress
End If
End With
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()
Dim objDict As Object
Dim ws As Worksheet
Dim xCell
Set ws = Sheets("Sheet2")
Set objDict = CreateObject("Scripting.Dictionary")
objDict.CompareMode = vbTextCompare
With ws
For Each xCell In .Range("A2:A" & .Cells(.Rows.Count, 1).End(xlUp).Row + 1)
If Not objDict.exists(xCell.Value) And Not xCell = vbNullString Then
objDict.Add xCell.Value, Nothing
End If
Next
End With
Me.ComboBox1.List = objDict.Keys
End SubHello,
Place this code in the userform module (to access the userform module, double click anywhere on the user form in visual basic editor):
Private Sub CommandButton1_Click()
Dim ws As Worksheet
Set ws = Sheets("Sheet2")
If Me.ComboBox1 = "" Then
MsgBox "Please select a style name."
Exit Sub
End If
Me.TextBox1 = WorksheetFunction.VLookup(Me.ComboBox1.Value, ws.Range("A:F"), 2, False)
Me.TextBox2 = WorksheetFunction.VLookup(Me.ComboBox1.Value, ws.Range("A:F"), 3, False)
Me.TextBox3 = WorksheetFunction.VLookup(Me.ComboBox1.Value, ws.Range("A:F"), 4, False)
Me.TextBox4 = WorksheetFunction.VLookup(Me.ComboBox1.Value, ws.Range("A:F"), 5, False)
Me.TextBox5 = WorksheetFunction.VLookup(Me.ComboBox1.Value, ws.Range("A:F"), 6, False)
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()
Me.ComboBox1.Clear
Dim ws As Worksheet
Dim lrow As Long
Dim i As Long
Set ws = Sheets("Sheet2")
lrow = ws.Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lrow
Me.ComboBox1.AddItem ws.Cells(i, 1)
Next i
End SubI also noticed that your userform has the combo box's row source set to the range Sheet2!A2:A100. You will need to delete this before the above code will work. To do that follow these steps:
1. Go to the userform and pull up the properties window in visual basic editor.
2. Click on the combo box and the properties window should change to reflect the combo box
3. Within the properties window, scroll down to "RowSource" and delete the text in this field
4. Save and exit visual basic editor.
If you change your sheet names at any point in the future, you will need to update the text in the above code from "Sheets("Sheet2")" to "Sheets("NewSheetName")". This appears twice in the code I wrote for you.
Let me know if you have any questions.
Very nice max,
now thinking
Private Sub ComboBox1_Change()
Dim ws As Worksheet
Set ws = Sheets("Sheet2")
If Me.ComboBox1 = "" Then
MsgBox "Please select a style name."
Exit Sub
End If
For n = 1 To 5
Me("TextBox" & n).Value = WorksheetFunction.VLookup(Me.ComboBox1.Value, ws.Range("A:F"), n + 1, False)
Next
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()
Dim rngList
Me.ComboBox1.Clear
With Sheets("Sheet2")
Set rngList = .Range("A2:A" & .Cells(.Rows.Count, 1).End(xlUp).Row)
Me.ComboBox1.List = Application.Transpose(rngList)
End With
End SubDear All,
Its working on attached sheet. but when i use this code on my another sheet its show me "Permission denied". Also i attached my sheet here. there combobox data came from AE column (Worksheet2) & my combobox in userform2. its open as click OPTION then click New Order. Pleas help me.
Wahid
remove the "RowSource" code from the combobox and change the references to suit range AE
Hello Wahid,
The changes would be for Userform2
Private Sub ComboBox1_Change()
Dim ws As Worksheet, n As Long
Set ws = Sheets("Sheet2")
If Me.ComboBox1 = "" Then
MsgBox "Please select a style name."
Exit Sub
End If
For n = 1 To 10
Me("TextBox" & n).Value = WorksheetFunction.VLookup(Me.ComboBox1.Value, ws.Range("AE:AO"), n + 1, False)
Next
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()
Dim rngList
Me.ComboBox1.Clear
With Sheets("Sheet2")
Set rngList = .Range("AE2:AE" & .Cells(.Rows.Count, 31).End(xlUp).Row)
Me.ComboBox1.List = Application.Transpose(rngList)
End With
End Subnoted with thanks to all. this is very much help for me.
Wahid
Dear Expert I am so sorry for again replying. actually it was working but suddenly now its show me "Could not set the list property. Invalid property arry index" please code in my sheet & upload.
Hello,
Its the same code and workbook you have.
Did you delete the range value from combobox1 "RowSource"
it will be something small
yes i am deleting combobox row source but not solve. see attachment please.
Hi
Its because you have only one value in the column
you will need to replace this
Private Sub UserForm_Initialize()
Dim rngList
Me.ComboBox1.Clear
With Sheets("Sheet2")
Set rngList = .Range("AE2:AE" & .Cells(.Rows.Count, 31).End(xlUp).Row)
If rngList.Rows.Count > 1 Then
Me.ComboBox1.List = Application.Transpose(rngList)
Else
Me.ComboBox1.AddItem rngList
End If
End With
End Subgreat thanks. its working good.
Dear Expert,
I want to add 2 combobox for vlookup means 2 different combobox for filling data different textbox data. i am trying & combobox 1 with releated textbox data showing ok but combobox 2 with releted data not working. please help me. I attached file. here userform 2 combobox 2 data collect from Sheet 2 AP & other text box data showing from AQ:AU. Userform showing-Option-New Order. Please help me.
Best Regards
Wahid
Dear Pike,
Please pay your attention.
Wahid
Hello, use
Private Sub ComboBox1_Change()
Dim ws As Worksheet, n As Long
Set ws = Sheets("Sheet2")
If Me.ComboBox1 = "" Then
MsgBox "Please select a style name."
Exit Sub
End If
For n = 1 To 10
Me("TextBox" & n).Value = WorksheetFunction.VLookup(Me.ComboBox1.Value, ws.Range("AE:AO"), n + 1, False)
Next
End Sub
Private Sub ComboBox2_Change()
Dim ws As Worksheet, n As Long
Set ws = Sheets("Sheet2")
If Me.ComboBox2 = "" Then
MsgBox "Please select a style name."
Exit Sub
End If
For n = 1 To 5
Me("TextBox" & 10 + n).Value = WorksheetFunction.VLookup(Me.ComboBox2.Value, ws.Range("AP:AV"), n + 1, False)
Next
End Sub
Private Sub UserForm_Initialize()
Dim rngList
Me.ComboBox1.Clear
With Sheets("Sheet2")
Set rngList = .Range("AE2:AE" & .Cells(.Rows.Count, 31).End(xlUp).Row)
If rngList.Rows.Count > 1 Then
Me.ComboBox1.List = Application.Transpose(rngList)
Else
Me.ComboBox1.AddItem rngList
End If
End With
Set rngList = Nothing
Me.ComboBox2.Clear
With Sheets("Sheet2")
Set rngList = .Range("AP2:Ap" & .Cells(.Rows.Count, 41).End(xlUp).Row)
If rngList.Rows.Count > 1 Then
Me.ComboBox2.List = Application.Transpose(rngList)
Else
Me.ComboBox2.AddItem rngList
End If
End With
End SubDon’t have an account yet? Register yourself now and be a part of our community!