How can I write a VBA code to transfer data from a userform to a specific worksheet in a specific workbook?
I have many workbooks that contain worksheets which I want input data to, but after I select a workbook by using a combo box selection.
How can I write a VBA code to transfer data from a userform to a specific worksheet in a specific workbook?
I have many workbooks that contain worksheets which I want input data to, but after I select a workbook by using a combo box selection.
First you have to create a user form. Give it a proper and descriptive name so that you can all it. (Userform1 is proper but not descriptive.) Then use code as shown below to call it.
Dim MyForm As Userform1 ' declare a variable
Set MyForm = New Userform1
MyForm.TextBox1.Value = "Hi!" ' preset values in the form
MyForm.Show ' the form takes over control
' code execution resumes here after the
' 'Hide' command in the form's code
' transfer data from the form to the worksheet
Unload MyForm ' now delete the form
Set MyForm = Nothing
Observe that the most important part of the above code isn't in it. It's in the code behind the form. There must be a 'Hide' command, usually given when the OK or Submit button is pressed.
After the form is hidden all of it is still in memory and accessible but control is returned to the main macro. So, at that point you can extract all the controls' values. You can place the code in the line I marked above or you can call a sub that transfers the date at that point. You can pass MyForm as an argument. Either way, you will need the components shown below.
1. Identify the specific workbook.
Dim Wb As Workbook ' declare a variable
Set Wb = Workbooks.Add("C:\[Mypath]\My Workbook's Name. xlsx")
2. Identify the specific worksheet.
Dim Ws As Worksheet ' declare a variable
Set Ws = Wb.Worksheets("My tab name")
3. Identify a specific cell.
Dim Cell As Range ' declare a variable
Set Cell = Ws.Cells(2, 1)
' or
Set Cell = Ws.Cells(2, "A")
' or
Set Cell = Ws.Range("A2")
4. Assign values to the specified cell.
Cell.Value = MyForm.TextBox1.Value
' or
Cell.Value = MyForm.Controls("TextBox1").Value
In fact, defining a specific cell with a named variable can be skipped as often as not because the code below will do the same job.
Ws.Cells(2, 1).Value = MyForm.TextBox1.Value
' or
Ws.Cells(2, "A").Value = MyForm.TextBox1.Value
' or
Ws.Range("A2").Value = MyForm.TextBox1.Value
One would name the cell only if there is more to do with it than assigning a value, such as formatting. One would use the syntax MyForm.Controls("TextBox1").Value when the control's name is supplied from an array.