Transfering Data From Userform To Sheet Under Relevant Column And Row

[paul_pearson]

Hi

I need some help please with the code transferring data from userform to sheet.The sheet will be protected == password is 1357
The userform looks at set Number and then finds it in the sheet and transfers the data.

Thanks

Paul

[Excel Fox]

I've made some modifications to your sheet. Notice the rngToCopy named range. You can move it around if required, but just keep whatever format you need. Also, revisions in the code are as follows

Private Sub UserForm_Initialize()

    Dim i As Long

'.
'.
'.
'.
'.

    With Worksheets("LENTICULAR-MEMBRANES")
        .Unprotect Password:="1357"
        .Protect UserInterfaceOnly:=True, Password:="1357"
    End With

End Sub

Private Sub CommandButton1_Click()
   
    Dim lngCol As Long
    Dim ctl As Object
    For Each ctl In Me.Controls
        If ctl.Tag <> vbNullString Then
            If ctl.Value = vbNullString Then MsgBox ctl.Tag: ctl.SetFocus: Exit Sub
        End If
    Next
    With Sheets("LENTICULAR-MEMBRANES")
    lngCol = .Rows("3:3").Find(What:="SET " & ComboBox2.Text, LookAt:=xlWhole).Column
       With .Cells(.Rows.Count, lngCol).End(xlUp).Offset(1).Cells(1)
            Sheets("LENTICULAR-MEMBRANES").Range("rngToCopy").Copy .Cells(1)
            .Resize(6).Value = Application.Transpose(Array("DATE", "LENTICULARS CHANGED", "MEMBRANES CHANGED", "VARIETY", "VOLUME", TextBox2.Text))
            .Offset(, 1).Resize(5) = Application.Transpose(Array(ComboBox1.Value, ComboBox3.Value, ComboBox4.Value, ComboBox5.Value, TextBox1.Text))
        End With
    End With
    
    For Each ctl In Me.Controls
        If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then ctl.Value = vbNullString
    Next
    
End Sub

[paul_pearson]

I`ve changed the layout to the data capture sheet.I have set up the sheet so data goes down the sheet for each set.With the userform can it look for the heading for the set and place the information going down the sheet.If I select C2 in the Userform Lenticular set then it places that info into C2 data capture sheet

Thanks

Paul

[paul_pearson]

This is altered to a set per sheet.On the userform depending which set selected depends on which sheet data goes...if C1 selected then data goes to C1 sheet etc..etc..

Paul

[paul_pearson]

bump

[Excel Fox]

Here's the revised code

Private Sub CommandButton1_Click()
   
    Dim lngCol As Long
    Dim ctl As Object
    For Each ctl In Me.Controls
        If ctl.Tag <> vbNullString Then
            If ctl.Value = vbNullString Then MsgBox ctl.Tag: ctl.SetFocus: Exit Sub
        End If
    Next
    With Sheets("LENTICULAR-MEMBRANES")
    lngCol = .Rows("2:2").Find(What:="SET " & ComboBox2.Text, LookAt:=xlWhole).Column
       With .Cells(.Rows.Count, lngCol).End(xlUp).Offset(1)
            .Resize(, 6) = Array(ComboBox1.Value, ComboBox3.Value, ComboBox4.Value, ComboBox5.Value, TextBox1.Text, TextBox2.Text)
        End With
    End With
    
    For Each ctl In Me.Controls
        If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then ctl.Value = vbNullString
    Next
    
End Sub

[paul_pearson]

Thanks

I get an error - subscript out of range

I think it may have to do with the sheet names....

I changed the sheet names in code but still error

Thanks

Paul

[bakerman]

Makes sence. Where is this sheet.

    With Worksheets("LENTICULAR-MEMBRANES")
        .Unprotect Password:="1357"
        .Protect UserInterfaceOnly:=True, Password:="1357"
    End With

Also you use the Tag-property to check for empty objects but in not one object you've filled in the Tag-property. In this case this will never work properly.

[paul_pearson]

Thanks

That sheet was removed...there are 5 sheets C1,C2,C3,C4,C5....I did enter these sheets in place of the LENTICULAR-MEMBRANES but it still gave error

I will fill in the Tag property

I will see how this goes

Paul

[bakerman]

But that part of code is still in your Userform_Initialize procedure so it will always throw an error when starting-up your UF.
The way you filled in your sheets is also wrong. I suppose you will have to use the value of your Lenticular set -object to write to the correct sheet.
It will become something like this

    With Sheets(ComboBox2.Value)
    lngCol = .Rows("2:2").Find(What:="SET " & ComboBox2.Text, LookAt:=xlWhole).Column
       With .Cells(.Rows.Count, lngCol).End(xlUp).Offset(1)
            .Resize(, 6) = Array(ComboBox1.Value, ComboBox3.Value, ComboBox4.Value, ComboBox5.Value, TextBox1.Text, TextBox2.Text)
        End With
    End With