CODEWITHSUNDEEP
vbaexcel
diem_L
diem_L

Reputation: 399

VBA Several UserForms export to Sheet

In my Workbook i have several identical UserForms. Only the name is different. For Example WKA1 / WKA2 / WKS1 / WKS2 / PM1 / PM2 and some more.

Now i want if i click on the CommandButton on the UserForm, that the Data in the TextBoxes will be saved on a extra Worksheet. I want that with a seperate Macro, so that i code for the CommandButton looks like this:

Private Sub CommandButton1_Click()
    Call Save_UF_Data
    [NameOfUF].Hide
End Sub

The Problem is, i don´t know how to write in the Macro "Save_UF_Data" that it always takes the TextBox from the UF which is open.

I already tried it with:

Me.Controls("TextBox1")

I hope someone can show me how i have to write the code that my macro will work for every UserForm in my Workbook.

This is my Macro so far:

Public Sub Save_UF_Data()
     Dim lastrow As Long
     Dim a As Integer
     Dim ws As Worksheet

     Set ws = Worksheet("UserForm_Data")
     lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
     a = 1

     Do
         ws.Range("A" & lastrow).Value = Me.Controls("Label" & a)    ' How do i have to change this part that it takes the value from the userform which is open?
         a = a + 1
         lastrow = lastrow + 1
     Loop Until a = 25
End Sub

Upvotes: 1

Views: 118

Answers (1)

FunThomas
FunThomas

Reputation: 29171

You could pass the calling form Object as Parameter to the Sub:

Private Sub CommandButton1_Click()
    Call Save_UF_Data(Me)
    [NameOfUF].Hide
End Sub

And the Sub would look like:

Public Sub Save_UF_Data(frm As UserForm)
    Debug.Print frm.Controls("Textbox1")
    (...)
End Sub

Have a look to How to loop through all controls in a form, including controls in a subform - Access 2007 to learn how to access the controls of a form

Upvotes: 3

Related Questions