Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Userform in excel

0

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.

Answer
Discuss

Answers

1

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.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login