Transfering Data From Userform To Sheet Under Relevant Column And Row

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

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

Code:

---

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

---

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

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

bump

Here's the revised code

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

---

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

Makes sence. Where is this sheet.

Code:

---

    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.

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

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

Code:

---

    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

---

Ok I will try again

Paul

I filled in the tag property,changed the sheets names but still get error

What have I missed.Combobox2 entry decides on which sheet to enter data....is this the wrong approached?

Thanks

Paul

Use this code

Code:

---

Option Explicit

Private Sub UserForm_Initialize()


    Dim i As Long
    Dim vList As Variant
    Dim vSht As Variant
   
    For i = CLng(Date - 2) To CLng(Date + 7)
        vList = vList & Format(i, "dd/mm/yyyy") & ","
    Next i
    vList = Left(vList, Len(vList) - 1)
    ComboBox1.List = Split(Mid(vList, 1), ",")
   
    vList = ("C1,C2,C3,C4,C5")
    ComboBox2.List = Split(Mid(vList, 1), ",")
   
    vList = vbNullString
    vList = "-,"
    For i = CLng(Date - 2) To CLng(Date + 7)
        vList = vList & Format(i, "dd/mm/yyyy") & ","
    Next
    vList = Left(vList, Len(vList) - 1)
    ComboBox3.List = Split(Mid(vList, 1), ",")
   
    vList = vbNullString
    vList = "-,"
    For i = CLng(Date - 2) To CLng(Date + 7)
        vList = vList & Format(i, "dd/mm/yyyy") & ","
    Next
    vList = Left(vList, Len(vList) - 1)
    ComboBox4.List = Split(Mid(vList, 1), ",")
   
    vList = ("AA,BB,CC,DD,EE")
    ComboBox5.List = Split(Mid(vList, 1), ",")
    For Each vSht In Array("C1", "C2", "C3", "C4", "C5")
        With Worksheets(vSht)
            .Unprotect Password:="1357"
            .Protect UserInterfaceOnly:=True, Password:="1357"
        End With
    Next


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(ComboBox2.Value)
      With .Cells(.Rows.Count, 2).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

---

By the way, please correct the sheet name from "C1 " to "C1"

Thanks Excel fox

Ok I got it now.That works
I modified another sheet and it now works as well
Thanks for all those who helped
Paul