VBA - using command button to copy userform text boxes to excel spreadsheet

    Hi there,


    I have created a user form in vba that someone would fill in various text boxes.


    What i now want is the info to be summarised in excel.


    The userform is held in sheet 1, so the idea is the user would input the information in the text boxes and then click the command button and the text boxes information would be copied to indiviual cells i.e textbox1 to a1 on sheet 1.


    I have tried by running a macro in excel to see the vb code but that does not work for a vba form.


    If anyone needs the file let me know and i will attach it

    Hi,


    Something like the following (in the code area attached to the form) worked for me.


    Private Sub SaveUserForm1()
    Dim boxName As String
    Dim i As Integer
    Dim myControl As Control


    i = 0
    For Each myControl In Controls
    If myControl.Name Like "TextBox*" Then
    i = i + 1
    boxName = "TextBox" & CStr(i)
    Sheets("Data").Cells(i + 1, 2).Value = Controls(boxName).Text
    End If
    Next
    End Sub


    The sheet I was putting everything was called "Data" and the form has loads of boxes on it. You might be better de-constructing the above to get it to do one box.


    Hope this helps


    John

    excellent,


    does anyone know what i would put if it was a label i.e Worksheets("Sheet1").Range("c2").Value = textbox1.text what would a label be if it was name h, i tried h.label but no success??

    It should work, but it could depend on how your "Bound Column" and/or "TextColumn" is set.


    This should only be an issue if it's a multicolumn combobox however.


    Anyway - try ComboBox1.Text and see if that does it.


    Otherwise, are you sure that "ComboBox1" is the name of the control you're working with? Also, are you sure a value has been selected in that combobox?


    Finally - if you're running this in some kind of a loop like above, are you sure that control is getting caught by the If statement? It may be that it's not getting to the line of code that you say is not working because of some slight coding error.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login