Customised UserForm

Stephenj

New Member
Joined
Nov 21, 2019
Messages
6
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
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Nov 21, 2019
Messages
6
Hi Yongle,

The reference is with Combobox1.Value

the combobox value is fetched from Column C

Thanks in advance
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
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

New Member
Joined
Nov 21, 2019
Messages
6

ADVERTISEMENT

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

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Nov 21, 2019
Messages
6

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,219
Office Version
  1. 365
Platform
  1. Windows
How exactly are you populating the combobox?
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Try modifying the code like this
- it converts the string to an integer and then the match shoould work

Add variable v
VBA Code:
Dim v As Integer
v = ComboBox1.value

Amend the match
VBA Code:
r = WorksheetFunction.Match(v, ws2.Range("C:C"), 0)
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
another option is to use Range.Find instead of Match

replace
VBA Code:
r = WorksheetFunction.Match(ComboBox1.Value, ws2.Range("C:C"), 0)
with
Code:
r = ws2.Range("C:C").Find(ComboBox1.Value, LookAt:=xlWhole).Row
 

Watch MrExcel Video

Forum statistics

Threads
1,123,017
Messages
5,599,362
Members
414,306
Latest member
Dennis_vdw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top