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

 

[Yongle]

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.

Which combobox ?
Which column in ws2 contains the value matching that combobox ?

 

[Stephenj]

Hi Yongle,

The reference is with Combobox1.Value

the combobox value is fetched from Column C

Thanks in advance

 

[Yongle]

In future, remember to click on </> icon and paste your code into the window that opens up
- it makes it much easier to read as you can see below
thanks

Try this:

Private Sub CommandButton1_Click()
    Dim r As Long
    Dim wb As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet
    
    Set wb = Workbooks.Open("\\xxx\YYY\zzz\Testing.xlsx")
    Set ws1 = wb.Sheets("Allocation_User1")
    Set ws2 = wb.Sheets("Feedback_User1")
    
    On Error Resume Next        'prevent code failing in unlikely event of value not being found
        r = WorksheetFunction.Match(ComboBox1.Value, ws2.Range("C:C"), 0)
        If Err.Number <> 0 Then GoTo Handling
    On Error GoTo 0
    With ws2
        .Cells(r, 1).Value = TextBox9.Value
        .Cells(r, 2).Value = TextBox11.Value
        .Cells(r, 3).Value = ComboBox2.Value
        .Cells(r, 4).Value = TextBox2.Value
        .Cells(r, 5).Value = TextBox3.Value
        .Cells(r, 6).Value = TextBox4.Value
        .Cells(r, 7).Value = TextBox4.Value
        .Cells(r, 8).Value = TextBox5.Value
        .Cells(r, 9).Value = TextBox6.Value
        .Cells(r, 10).Value = ComboBox3.Value
        .Cells(r, 11).Value = ComboBox4.Value
        .Cells(r, 12).Value = ComboBox5.Value
        .Cells(r, 13).Value = ComboBox6.Value
        .Cells(r, 14).Value = ComboBox7.Value
        .Cells(r, 15).Value = TextBox10.Value
        .Cells(r, 16).Value = ComboBox9.Value
        .Cells(r, 17).Value = ComboBox10.Value
        .Cells(r, 18).Value = TextBox7.Value
    End With
    wb.Save
    wb.Close False
    Unload Me
    UserForm1.Show
    Exit Sub
Handling:
MsgBox "value not found", vbExclamation, ""
End Sub

 

[Stephenj]

Thank you Yongle for the response

Unfortunately the match function does not work properly.

The comboBox1.Value is fetched from the same column where I wanted the return value to be sent, but when the return code is running it is not able to find the match case. and throws the error as match not found.

Any clue on the error!

Thanks in advance

 

[Yongle]

It may be the type of data in the ComboBox
- comboboxes contain strings
- may need to convert the data so that VBA can see the match

Please provide 5 examples of the contents of ComboBox1
How is the data formatted in column C in ws2 ?

 

[Stephenj]

the comboBox value is only numeric, for example it would range from 10486 to 12000

the concept is user has to select one of his tickets which is listed in the combobox and complete the task and submit, while the submit button is clicked the entire userform data to be updated as against the ticket number in the ws2

currently I have run a code to copy the data to the ws1 and then do a vlookup manually to get the field updated

 

[Norie]

How exactly are you populating the combobox?

 

[Yongle]

Try modifying the code like this
- it converts the string to an integer and then the match shoould work

Add variable v

Dim v As Integer
v = ComboBox1.value

Amend the match

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

 

[Yongle]

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