Selected Answer
Mussa
The problem is that in your UserForm_Initialize macro, your With statement refers to a range (A2:A10) to which you apply offsets to populate Combobox lists but that does NOT work for a Textbox. The latter can contain only one value so I've changed your macro to show a - (until there's a change), shown in bold below:
Private Sub UserForm_Initialize()
With Sheets("Sheet1")
With .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))
ComboBox1.List = .Value
ComboBox2.List = .Offset(, 1).Value
ComboBox3.List = .Offset(, 2).Value
ComboBox4.List = .Offset(, 3).Value
TextBox1.Value = "-" '.Offset(, 4).Value 'TextBox can contain only one value
End With
End With
Then in the ComboBox1_Change macro, you find the Code but you try to get a price using the line
TextBox1.Value = .Offset(, 4).Value
but offset from which cell?
The ComboBox1_Change macro could be changed to find the last price from sheet2. Here I've re-used/modified the loop using in the CommandButton1_Click macro to seek out the last price (changes in bold):
Private Sub ComboBox1_Change()
Dim c As Range
With Sheets("Sheet1")
Set c = .Range("A:A").Find(What:=ComboBox1.Value, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If Not c Is Nothing Then
ComboBox2.Value = c.Offset(, 1).Value
ComboBox3.Value = c.Offset(, 2).Value
ComboBox4.Value = c.Offset(, 3).Value
'Find last price for Code
ss = Sheets("Sheet1").Range("a" & Rows.Count).End(xlUp).Row
' See if Code already exists...
For n = 2 To ss
If Range("A" & n) = ComboBox1.Value Then
'if so get price from last used cell in row
TextBox1.Value = Cells(n, Cells(n, Columns.Count).End(xlToLeft).Column)
Exit Sub
End If
Next n
'if not found, blank price
TextBox1.Value = "-"
End If
End With
End Sub
Notice that if there is no price in sheet2, I've said "
if not found, blank price". That does not reset the other fields but if ComboBox1 might be overwritten with a new code then you should consider searching sheet1 (to check it's new) and blank ComboBoxes 2-4 if it is..
