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
 

Stephenj

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

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

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Stephenj

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

Perfectly executing my requirement.

You are a master Yongle.

Thank you
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
(y)Thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,123,006
Messages
5,599,318
Members
414,304
Latest member
sarao18592

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