Customised UserForm

[Stephenj]

Hi All,

It would be of great help if I could get a solution for my long searching query.

I have a Combobox in Userform which fetches the data from a worksheet, on selecting a specific record the rest fields would get auto populated, now the user has to fill few details in the available textboxes. Once the data is captured completely and clicking submit the userform data moves another worksheet.

My problem is im not able to move the data against the value selected in the combobox rather the data gets saved in the last empty row.

Below is my code.

Private Sub CommandButton1_Click()
Dim iRow As Long
Dim wb As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set wb = Workbooks.Open("\\xxx\YYY\zzz\Testing.xlsx")
Set ws1 = wb.Sheets("Allocation_User1")
Set ws2 = wb.Sheets("Feedback_User1")
EmptyRow = WorksheetFunction.ws2.CountA(Range("A:A")) + 1
Cells(EmptyRow, 1).Value = TextBox9.Value
Cells(EmptyRow, 2).Value = TextBox11.Value
Cells(EmptyRow, 3).Value = ComboBox2.Value
Cells(EmptyRow, 4).Value = TextBox2.Value
Cells(EmptyRow, 5).Value = TextBox3.Value
Cells(EmptyRow, 6).Value = TextBox4.Value
Cells(EmptyRow, 7).Value = TextBox4.Value
Cells(EmptyRow, 8).Value = TextBox5.Value
Cells(EmptyRow, 9).Value = TextBox6.Value
Cells(EmptyRow, 10).Value = ComboBox3.Value
Cells(EmptyRow, 11).Value = ComboBox4.Value
Cells(EmptyRow, 12).Value = ComboBox5.Value
Cells(EmptyRow, 13).Value = ComboBox6.Value
Cells(EmptyRow, 14).Value = ComboBox7.Value
Cells(EmptyRow, 15).Value = TextBox10.Value
Cells(EmptyRow, 16).Value = ComboBox9.Value
Cells(EmptyRow, 17).Value = ComboBox10.Value
Cells(EmptyRow, 18).Value = TextBox7.Value

ActiveWorkbook.Save
ActiveWorkbook.Close
Unload Me
UserForm1.Show
End Sub

 

[Stephenj]

Hi Yongle,

Still not able to get through, getting the same error even modifying with interger.

 

[Yongle]

see post#10

 

[Stephenj]

another option is to use Range.Find instead of Match

replace

r = WorksheetFunction.Match(ComboBox1.Value, ws2.Range("C:C"), 0)

with

r = ws2.Range("C:C").Find(ComboBox1.Value, LookAt:=xlWhole).Row

Alas!

Perfectly executing my requirement.

You are a master Yongle.

Thank you

 

[Yongle]

Thanks for the feedback